CREATE SEQUENCE ITEM_AUTO_ID_SEQ
    INCREMENT BY 1 -- 每次加几个
    START WITH 1 -- 从1开始计数
    NOMAXvalue -- 不设置最大值
    NOCYCLE -- 一直累加，不循环
    NOCACHE;

CREATE SEQUENCE OVERSEA_DELIVERY_NOTE_DTL_SEQ
    INCREMENT BY 1 -- 每次加几个
    START WITH 1 -- 从1开始计数
    NOMAXvalue -- 不设置最大值
    NOCYCLE -- 一直累加，不循环
    NOCACHE;

CREATE SEQUENCE OVERSEA_DN_DTL_SEQ
    INCREMENT BY 1 -- 每次加几个
    START WITH 1 -- 从1开始计数
    NOMAXvalue -- 不设置最大值
    NOCYCLE -- 一直累加，不循环
    NOCACHE;

CREATE SEQUENCE OVERSEA_OPERATE_LOG_SEQ
    INCREMENT BY 1 -- 每次加几个
    START WITH 1 -- 从1开始计数
    NOMAXvalue -- 不设置最大值
    NOCYCLE -- 一直累加，不循环
    NOCACHE;

CREATE SEQUENCE OVERSEA_OTHER_DTL_SEQ
    INCREMENT BY 1 -- 每次加几个
    START WITH 1 -- 从1开始计数
    NOMAXvalue -- 不设置最大值
    NOCYCLE -- 一直累加，不循环
    NOCACHE;

CREATE SEQUENCE OVERSEA_PACKAGE_LIST_BOX_SEQ
    INCREMENT BY 1 -- 每次加几个
    START WITH 1 -- 从1开始计数
    NOMAXvalue -- 不设置最大值
    NOCYCLE -- 一直累加，不循环
    NOCACHE;

CREATE SEQUENCE OVERSEA_PACKAGE_LIST_DTL_SEQ
    INCREMENT BY 1 -- 每次加几个
    START WITH 1 -- 从1开始计数
    NOMAXvalue -- 不设置最大值
    NOCYCLE -- 一直累加，不循环
    NOCACHE;

CREATE SEQUENCE OVERSEA_PURCHASE_ORDER_DTL_SEQ
    INCREMENT BY 1 -- 每次加几个
    START WITH 1 -- 从1开始计数
    NOMAXvalue -- 不设置最大值
    NOCYCLE -- 一直累加，不循环
    NOCACHE;

CREATE SEQUENCE OVERSEA_REQUISITION_DTL_SEQ
    INCREMENT BY 1 -- 每次加几个
    START WITH 1 -- 从1开始计数
    NOMAXvalue -- 不设置最大值
    NOCYCLE -- 一直累加，不循环
    NOCACHE;

CREATE SEQUENCE OVERSEA_REQUISITION_SEQ
    INCREMENT BY 1 -- 每次加几个
    START WITH 1 -- 从1开始计数
    NOMAXvalue -- 不设置最大值
    NOCYCLE -- 一直累加，不循环
    NOCACHE;

CREATE SEQUENCE OVERSEA_WHS_DUMP_DTL_SEQ
    INCREMENT BY 1 -- 每次加几个
    START WITH 1 -- 从1开始计数
    NOMAXvalue -- 不设置最大值
    NOCYCLE -- 一直累加，不循环
    NOCACHE;

CREATE SEQUENCE OVERSEA_WHS_OUT_DTL_SEQ
    INCREMENT BY 1 -- 每次加几个
    START WITH 1 -- 从1开始计数
    NOMAXvalue -- 不设置最大值
    NOCYCLE -- 一直累加，不循环
    NOCACHE;

CREATE SEQUENCE OVERSEA_WHS_OUT_SEQ
    INCREMENT BY 1 -- 每次加几个
    START WITH 1 -- 从1开始计数
    NOMAXvalue -- 不设置最大值
    NOCYCLE -- 一直累加，不循环
    NOCACHE;



create table OVERSEA_DELIVERY_NOTE
(
    UUID_DELIVERY_NOTE VARCHAR2(32) not null
        constraint OVERSEA_DELIVERY_NOTE_PK
            primary key,
    SAP_DELIVERY_NOTE VARCHAR2(16),
    SAP_PACKAGE_LIST VARCHAR2(16),
    CABINET_NUMBER VARCHAR2(20),
    DELIVERY_TYPE VARCHAR2(8),
    SPMS_STATUS VARCHAR2(2) default '0',
    WERKS_RECEIVE VARCHAR2(8),
    WERKS_DELIVERY VARCHAR2(8),
    CREATE_DATE VARCHAR2(12),
    ARRIVAL_DATE VARCHAR2(16),
    REMARKS VARCHAR2(255),
    COMMENTS VARCHAR2(255),
    POSTING_DATE VARCHAR2(16),
    SAP_VOUCHER_NUMBER VARCHAR2(32),
    SAP_VOUCHER_YEAR VARCHAR2(8),
    CANCEL_POSTING_DATE VARCHAR2(16),
    CANCEL_VOUCHER_NUMBER VARCHAR2(32),
    CANCEL_VOUCHER_YEAR VARCHAR2(8),
    SAP_REMARK VARCHAR2(255),
    DATA_STATUS VARCHAR2(2) default '0',
    DATA_VERSION NUMBER default "DATA_VERSION"."NEXTVAL",
    CREATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'),
    UPDATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'),
    ACTUAL_WERKS_RECEIVE VARCHAR2(6),
    OA_WORKFLOW VARCHAR2(16),
    OA_BIANHAO VARCHAR2(32),
    OA_REMARK VARCHAR2(255),
    SUBMIT_OA_DATE VARCHAR2(12),
    OA_APPROVAL_DATE VARCHAR2(12),
    OA_FIELD VARCHAR2(16),
    UNDER_VOUCHER_NUMBER VARCHAR2(32),
    UNDER_VOUCHER_YEAR VARCHAR2(8),
    UNDER_POSTING_DATE VARCHAR2(16),
    CANCEL_UNDER_POSTING_DATE VARCHAR2(16),
    CANCEL_UNDER_VOUCHER_NUMBER VARCHAR2(32),
    CANCEL_UNDER_VOUCHER_YEAR VARCHAR2(8),
    ACTUAL_WHS_LOCATION_CODE_RECEIVE VARCHAR2(12),
    COST_CENTER VARCHAR2(32)
)
/

comment on table OVERSEA_DELIVERY_NOTE is '海外仓交货单表'
/

comment on column OVERSEA_DELIVERY_NOTE.UUID_DELIVERY_NOTE is '交货单号uuid'
/

comment on column OVERSEA_DELIVERY_NOTE.SAP_DELIVERY_NOTE is '交货单号'
/

comment on column OVERSEA_DELIVERY_NOTE.SAP_PACKAGE_LIST is '装箱单号'
/

comment on column OVERSEA_DELIVERY_NOTE.CABINET_NUMBER is '货柜号'
/

comment on column OVERSEA_DELIVERY_NOTE.DELIVERY_TYPE is '交货类型'
/

comment on column OVERSEA_DELIVERY_NOTE.SPMS_STATUS is 'SPMS收货单据状态(默认0待到货;1收货中/SAP凭证撤销;2入库完成/提交OA成功;3提交OA失败;4OA审批不通过;5OA审批通过/提交SAP失败;6收货多收提交SAP失败;7收货多收提交SAP成功;8收货少收提交SAP失败;9收货少收提交SAP成功;10提交SAP完成;11收货完成;12关闭/废单;)'
/

comment on column OVERSEA_DELIVERY_NOTE.WERKS_RECEIVE is '收货工厂'
/

comment on column OVERSEA_DELIVERY_NOTE.WERKS_DELIVERY is '发货工厂'
/

comment on column OVERSEA_DELIVERY_NOTE.CREATE_DATE is '创建日期'
/

comment on column OVERSEA_DELIVERY_NOTE.ARRIVAL_DATE is '到货日期(第一次生成入库任务日期)'
/

comment on column OVERSEA_DELIVERY_NOTE.REMARKS is 'SPMS备注信息'
/

comment on column OVERSEA_DELIVERY_NOTE.COMMENTS is 'SPMS评论/预留'
/

comment on column OVERSEA_DELIVERY_NOTE.POSTING_DATE is '过账日期/少收提交SAP入库凭证'
/

comment on column OVERSEA_DELIVERY_NOTE.SAP_VOUCHER_NUMBER is 'SAP凭证号/少收提交SAP入库凭证'
/

comment on column OVERSEA_DELIVERY_NOTE.SAP_VOUCHER_YEAR is 'SAP凭证年份/少收提交SAP入库凭证'
/

comment on column OVERSEA_DELIVERY_NOTE.CANCEL_POSTING_DATE is '撤销SAP过账日期/少收提交SAP入库凭证'
/

comment on column OVERSEA_DELIVERY_NOTE.CANCEL_VOUCHER_NUMBER is '撤销SAP凭证/少收提交SAP入库凭证'
/

comment on column OVERSEA_DELIVERY_NOTE.CANCEL_VOUCHER_YEAR is '撤销SAP凭证年份/少收提交SAP入库凭证'
/

comment on column OVERSEA_DELIVERY_NOTE.SAP_REMARK is 'SAP交货单抬头文本'
/

comment on column OVERSEA_DELIVERY_NOTE.DATA_STATUS is '数据状态(默认0能被SAP更新数据；1禁止更新数据；)'
/

comment on column OVERSEA_DELIVERY_NOTE.DATA_VERSION is '数据版本号'
/

comment on column OVERSEA_DELIVERY_NOTE.CREATE_TIME is '创建时间'
/

comment on column OVERSEA_DELIVERY_NOTE.UPDATE_TIME is '更新时间'
/

comment on column OVERSEA_DELIVERY_NOTE.ACTUAL_WERKS_RECEIVE is '实际收货工厂(交货单分批收不允许跨工厂收货)'
/

comment on column OVERSEA_DELIVERY_NOTE.OA_WORKFLOW is 'OA流程编号'
/

comment on column OVERSEA_DELIVERY_NOTE.OA_BIANHAO is 'OA单据编号'
/

comment on column OVERSEA_DELIVERY_NOTE.OA_REMARK is '提交OA审批备注'
/

comment on column OVERSEA_DELIVERY_NOTE.SUBMIT_OA_DATE is '提交OA日期'
/

comment on column OVERSEA_DELIVERY_NOTE.OA_APPROVAL_DATE is 'OA审批通过日期'
/

comment on column OVERSEA_DELIVERY_NOTE.OA_FIELD is 'OA预留字段/存储SPMS生成提交OA流水单据号'
/

comment on column OVERSEA_DELIVERY_NOTE.UNDER_VOUCHER_NUMBER is 'SAP凭证号/少收凭证'
/

comment on column OVERSEA_DELIVERY_NOTE.UNDER_VOUCHER_YEAR is 'SAP凭证年份/少收凭证'
/

comment on column OVERSEA_DELIVERY_NOTE.UNDER_POSTING_DATE is '过账日期/少收凭证'
/

comment on column OVERSEA_DELIVERY_NOTE.CANCEL_UNDER_POSTING_DATE is '撤销SAP过账日期/少收凭证'
/

comment on column OVERSEA_DELIVERY_NOTE.CANCEL_UNDER_VOUCHER_NUMBER is '撤销SAP凭证/少收凭证'
/

comment on column OVERSEA_DELIVERY_NOTE.CANCEL_UNDER_VOUCHER_YEAR is '撤销SAP凭证年份/少收凭证'
/

comment on column OVERSEA_DELIVERY_NOTE.ACTUAL_WHS_LOCATION_CODE_RECEIVE is '实际收货仓库(第一次拆出入库任务时写入)'
/

comment on column OVERSEA_DELIVERY_NOTE.COST_CENTER is '成本中心'
/

create trigger TR_OVERSEA_DELIVERY_NOTE_BEF_UPDATE
    before update
    on OVERSEA_DELIVERY_NOTE
    for each row
BEGIN
    :new.UPDATE_TIME := TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss');
    :new.DATA_VERSION := DATA_VERSION.nextval;
END;
/






create table OVERSEA_DELIVERY_NOTE_DTL
(
    AUTO_ID_DELIVERY_NOTE NUMBER default "OVERSEA_DELIVERY_NOTE_DTL_SEQ"."NEXTVAL" not null
        constraint OVERSEA_DELIVERY_NOTE_DTL_PK
            primary key,
    SAP_DELIVERY_NOTE VARCHAR2(16),
    SAP_DELIVERY_NOTE_ITEM VARCHAR2(6),
    MATERIAL_NO VARCHAR2(32),
    BASIC_UNIT VARCHAR2(6),
    UNIT_SALES VARCHAR2(6),
    RECEIVABLE_QTY_BASIC_UNIT NUMBER default 0,
    RECEIVABLE_QTY_UNIT_SALES NUMBER default 0,
    ACTUAL_QTY_BASIC_UNIT NUMBER default 0,
    ACTUAL_QTY_UNIT_SALES NUMBER default 0,
    PURCHASE_NO VARCHAR2(16),
    PURCHASE_NO_ITEM VARCHAR2(6),
    REMARKS VARCHAR2(255),
    WERKS_DELIVERY VARCHAR2(6),
    WHS_LOCATION_CODE_DELIVERY VARCHAR2(8),
    WERKS_RECEIVE VARCHAR2(6),
    WHS_LOCATION_CODE_RECEIVE VARCHAR2(8),
    OVERCHARGED_QTY_BASIC_UNIT NUMBER default 0,
    OVERCHARGED_QTY_UNIT_SALES NUMBER default 0,
    UNDERCHARGED_QTY_BASIC_UNIT NUMBER default 0,
    UNDERCHARGED_QTY_UNIT_SALES NUMBER default 0,
    MOVEMENT_TYPE VARCHAR2(6),
    SAP_REMARK VARCHAR2(255),
    COLOR_NO VARCHAR2(64),
    DATA_STATUS VARCHAR2(2) default '0',
    DATA_VERSION NUMBER default "DATA_VERSION"."NEXTVAL",
    CREATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'),
    UPDATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss')
)
/

comment on table OVERSEA_DELIVERY_NOTE_DTL is '海外仓交货单明细表'
/

comment on column OVERSEA_DELIVERY_NOTE_DTL.AUTO_ID_DELIVERY_NOTE is '交货单明细自增ID'
/

comment on column OVERSEA_DELIVERY_NOTE_DTL.SAP_DELIVERY_NOTE is '交货单号'
/

comment on column OVERSEA_DELIVERY_NOTE_DTL.SAP_DELIVERY_NOTE_ITEM is '交货单行号'
/

comment on column OVERSEA_DELIVERY_NOTE_DTL.MATERIAL_NO is '物料编号'
/

comment on column OVERSEA_DELIVERY_NOTE_DTL.BASIC_UNIT is '基本单位'
/

comment on column OVERSEA_DELIVERY_NOTE_DTL.UNIT_SALES is '销售单位'
/

comment on column OVERSEA_DELIVERY_NOTE_DTL.RECEIVABLE_QTY_BASIC_UNIT is '基本单位发货数量/海外应收基本单位数量'
/

comment on column OVERSEA_DELIVERY_NOTE_DTL.RECEIVABLE_QTY_UNIT_SALES is '销售单位发出的数量/海外应收销售单位数量'
/

comment on column OVERSEA_DELIVERY_NOTE_DTL.ACTUAL_QTY_BASIC_UNIT is '实收基本单位数量'
/

comment on column OVERSEA_DELIVERY_NOTE_DTL.ACTUAL_QTY_UNIT_SALES is '实收销售单位数量'
/

comment on column OVERSEA_DELIVERY_NOTE_DTL.PURCHASE_NO is '参考单据的单据编号/公司间采购订单号'
/

comment on column OVERSEA_DELIVERY_NOTE_DTL.PURCHASE_NO_ITEM is '参考项目的项目号/公司间采购订单行项目'
/

comment on column OVERSEA_DELIVERY_NOTE_DTL.REMARKS is 'SPMS备注信息'
/

comment on column OVERSEA_DELIVERY_NOTE_DTL.WERKS_DELIVERY is '发货工厂'
/

comment on column OVERSEA_DELIVERY_NOTE_DTL.WHS_LOCATION_CODE_DELIVERY is '发货库存地点'
/

comment on column OVERSEA_DELIVERY_NOTE_DTL.WERKS_RECEIVE is '收货工厂'
/

comment on column OVERSEA_DELIVERY_NOTE_DTL.WHS_LOCATION_CODE_RECEIVE is '收货库存地点'
/

comment on column OVERSEA_DELIVERY_NOTE_DTL.OVERCHARGED_QTY_BASIC_UNIT is '多收基本单位数量'
/

comment on column OVERSEA_DELIVERY_NOTE_DTL.OVERCHARGED_QTY_UNIT_SALES is '多收销售单位数量'
/

comment on column OVERSEA_DELIVERY_NOTE_DTL.UNDERCHARGED_QTY_BASIC_UNIT is '少收基本单位数量'
/

comment on column OVERSEA_DELIVERY_NOTE_DTL.UNDERCHARGED_QTY_UNIT_SALES is '少收销售单位数量'
/

comment on column OVERSEA_DELIVERY_NOTE_DTL.MOVEMENT_TYPE is '移动类型（库存管理）'
/

comment on column OVERSEA_DELIVERY_NOTE_DTL.SAP_REMARK is 'SAP交货单行备注'
/

comment on column OVERSEA_DELIVERY_NOTE_DTL.COLOR_NO is '色号批次'
/

comment on column OVERSEA_DELIVERY_NOTE_DTL.DATA_STATUS is '数据状态(默认0能被SAP更新数据；1禁止更新数据；)'
/

comment on column OVERSEA_DELIVERY_NOTE_DTL.DATA_VERSION is '数据版本号'
/

comment on column OVERSEA_DELIVERY_NOTE_DTL.CREATE_TIME is '创建时间'
/

comment on column OVERSEA_DELIVERY_NOTE_DTL.UPDATE_TIME is '更新时间'
/

create trigger TR_OVERSEA_DELIVERY_NOTE_DTL_BEF_UPDATE
    before update
    on OVERSEA_DELIVERY_NOTE_DTL
    for each row
BEGIN
    :new.UPDATE_TIME := TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss');
    :new.DATA_VERSION := DATA_VERSION.nextval;
END;
/



create table OVERSEA_DN
(
    UUID_DELIVERY_NOTE VARCHAR2(32) not null
        constraint OVERSEA_DN_PK
            primary key,
    SAP_DELIVERY_NOTE VARCHAR2(16),
    SPMS_STATUS VARCHAR2(2) default '0',
    WERKS_RECEIVE VARCHAR2(8),
    WHS_LOCATION_CODE_RECEIVE VARCHAR2(6),
    SAP_CREATE_DATE VARCHAR2(16),
    DELIVERY_DATE VARCHAR2(16),
    ARRIVAL_DATE VARCHAR2(16),
    REMARKS VARCHAR2(255),
    COMMENTS VARCHAR2(255),
    OPERATE_USER VARCHAR2(64),
    POSTING_DATE VARCHAR2(16),
    SAP_VOUCHER_NUMBER VARCHAR2(32),
    SAP_VOUCHER_YEAR VARCHAR2(8),
    CANCEL_POSTING_DATE VARCHAR2(16),
    CANCEL_VOUCHER_NUMBER VARCHAR2(32),
    CANCEL_VOUCHER_YEAR VARCHAR2(8),
    UNDER_VOUCHER_NUMBER VARCHAR2(32),
    UNDER_VOUCHER_YEAR VARCHAR2(8),
    UNDER_POSTING_DATE VARCHAR2(16),
    CANCEL_UNDER_POSTING_DATE VARCHAR2(16),
    CANCEL_UNDER_VOUCHER_NUMBER VARCHAR2(32),
    CANCEL_UNDER_VOUCHER_YEAR VARCHAR2(8),
    COST_CENTER VARCHAR2(32),
    DATA_STATUS VARCHAR2(2) default '0',
    DATA_VERSION NUMBER default "DATA_VERSION"."NEXTVAL",
    CREATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'),
    UPDATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'),
    BUSINESS_TYPE VARCHAR2(4)
)
/

comment on table OVERSEA_DN is '海外仓交货单表(空运人带/转储/本地采购)'
/

comment on column OVERSEA_DN.UUID_DELIVERY_NOTE is '交货单号uuid'
/

comment on column OVERSEA_DN.SAP_DELIVERY_NOTE is '交货单号'
/

comment on column OVERSEA_DN.SPMS_STATUS is 'SPMS收货单据状态(默认0待到货;1入库中/审批不通过;2入库完成/待审批;3审批通过;4按应收提交SAP成功/SPMS按实收入库存;5按应收提交SAP失败;6收货少收提交SAP失败;7收货少收提交SAP成功;8提交SAP完成;9收货完成;10关闭/废单;)'
/

comment on column OVERSEA_DN.WERKS_RECEIVE is '收货工厂'
/

comment on column OVERSEA_DN.WHS_LOCATION_CODE_RECEIVE is '收货仓库地址'
/

comment on column OVERSEA_DN.SAP_CREATE_DATE is 'DN创建日期'
/

comment on column OVERSEA_DN.DELIVERY_DATE is 'SAP DN交货日期'
/

comment on column OVERSEA_DN.ARRIVAL_DATE is '到货日期(用户确认到货日期)'
/

comment on column OVERSEA_DN.REMARKS is 'SPMS备注信息'
/

comment on column OVERSEA_DN.COMMENTS is 'SPMS评论/预留'
/

comment on column OVERSEA_DN.OPERATE_USER is '操作员'
/

comment on column OVERSEA_DN.POSTING_DATE is '过账日期/按应收入库凭证'
/

comment on column OVERSEA_DN.SAP_VOUCHER_NUMBER is 'SAP凭证号/按应收入库凭证'
/

comment on column OVERSEA_DN.SAP_VOUCHER_YEAR is 'SAP凭证年份/按应收入库凭证'
/

comment on column OVERSEA_DN.CANCEL_POSTING_DATE is '撤销SAP过账日期/按应收入库凭证'
/

comment on column OVERSEA_DN.CANCEL_VOUCHER_NUMBER is '撤销SAP凭证/按应收入库凭证'
/

comment on column OVERSEA_DN.CANCEL_VOUCHER_YEAR is '撤销SAP凭证年份/按应收入库凭证'
/

comment on column OVERSEA_DN.UNDER_VOUCHER_NUMBER is 'SAP凭证号/少收凭证'
/

comment on column OVERSEA_DN.UNDER_VOUCHER_YEAR is 'SAP凭证年份/少收凭证'
/

comment on column OVERSEA_DN.UNDER_POSTING_DATE is 'SAP过账日期/少收凭证'
/

comment on column OVERSEA_DN.CANCEL_UNDER_POSTING_DATE is '撤销SAP过账日期/少收凭证'
/

comment on column OVERSEA_DN.CANCEL_UNDER_VOUCHER_NUMBER is '撤销SAP凭证/少收凭证'
/

comment on column OVERSEA_DN.CANCEL_UNDER_VOUCHER_YEAR is '撤销SAP凭证年份/少收凭证'
/

comment on column OVERSEA_DN.COST_CENTER is '成本中心'
/

comment on column OVERSEA_DN.DATA_STATUS is '数据状态(默认0能被SAP更新数据；1禁止更新数据；)'
/

comment on column OVERSEA_DN.DATA_VERSION is '数据版本号'
/

comment on column OVERSEA_DN.CREATE_TIME is '创建时间'
/

comment on column OVERSEA_DN.UPDATE_TIME is '更新时间'
/

comment on column OVERSEA_DN.BUSINESS_TYPE is '业务标识(01空运人带;02本地采购;03海外转储;05退货订单;)'
/

create trigger TR_OVERSEA_DN_BEF_UPDATE
    before update
    on OVERSEA_DN
    for each row
BEGIN
    :new.UPDATE_TIME := TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss');
    :new.DATA_VERSION := DATA_VERSION.nextval;
END;
/




create table OVERSEA_DN_DTL
(
    AUTO_ID_DN NUMBER default "OVERSEA_DN_DTL_SEQ"."NEXTVAL" not null
        constraint OVERSEA_DN_DTL_PK
            primary key,
    SAP_DELIVERY_NOTE VARCHAR2(16),
    SAP_DELIVERY_NOTE_ITEM VARCHAR2(6),
    SAP_PURCHASE_ORDER VARCHAR2(16),
    SAP_PURCHASE_ORDER_ITEM VARCHAR2(6),
    WERKS_DELIVERY VARCHAR2(6),
    WHS_LOCATION_CODE_DELIVERY VARCHAR2(6),
    WERKS_RECEIVE VARCHAR2(6),
    WHS_LOCATION_CODE_RECEIVE VARCHAR2(6),
    MATERIAL_NO VARCHAR2(32),
    BASIC_UNIT VARCHAR2(6),
    UNIT_SALES VARCHAR2(6),
    RECEIVABLE_QTY_BASIC_UNIT NUMBER default 0,
    RECEIVABLE_QTY_UNIT_SALES NUMBER default 0,
    ACTUAL_QTY_BASIC_UNIT NUMBER default 0,
    ACTUAL_QTY_UNIT_SALES NUMBER default 0,
    STORAGE_INFO VARCHAR2(2000),
    REMARKS VARCHAR2(255),
    COMMENTS VARCHAR2(255),
    BOX_NOTE VARCHAR2(32),
    BOX_NOTE_ITEM VARCHAR2(6),
    DOMESTIC_ACTUAL_QTY NUMBER default 0,
    DEMAND_NO VARCHAR2(32),
    DEMAND_ITEM VARCHAR2(6),
    DEMANDER VARCHAR2(64),
    DATA_STATUS VARCHAR2(2) default '0',
    DATA_VERSION NUMBER default "DATA_VERSION"."NEXTVAL",
    CREATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'),
    UPDATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss')
)
/

comment on table OVERSEA_DN_DTL is '交货单明细采购订单明细关系表'
/

comment on column OVERSEA_DN_DTL.AUTO_ID_DN is '交货单明细表自增ID'
/

comment on column OVERSEA_DN_DTL.SAP_DELIVERY_NOTE is '交货单号'
/

comment on column OVERSEA_DN_DTL.SAP_DELIVERY_NOTE_ITEM is '交货单行号'
/

comment on column OVERSEA_DN_DTL.SAP_PURCHASE_ORDER is '参考采购凭证号PO'
/

comment on column OVERSEA_DN_DTL.SAP_PURCHASE_ORDER_ITEM is '参考采购凭证号PO行号'
/

comment on column OVERSEA_DN_DTL.WERKS_DELIVERY is '发货工厂'
/

comment on column OVERSEA_DN_DTL.WHS_LOCATION_CODE_DELIVERY is '发货仓库地址'
/

comment on column OVERSEA_DN_DTL.WERKS_RECEIVE is '收货工厂'
/

comment on column OVERSEA_DN_DTL.WHS_LOCATION_CODE_RECEIVE is '收货仓库地址'
/

comment on column OVERSEA_DN_DTL.MATERIAL_NO is '物料编号'
/

comment on column OVERSEA_DN_DTL.BASIC_UNIT is '基本单位'
/

comment on column OVERSEA_DN_DTL.UNIT_SALES is '销售单位'
/

comment on column OVERSEA_DN_DTL.RECEIVABLE_QTY_BASIC_UNIT is '交货数量/应收基本单位数量'
/

comment on column OVERSEA_DN_DTL.RECEIVABLE_QTY_UNIT_SALES is '交货数量/应收销售单位数量'
/

comment on column OVERSEA_DN_DTL.ACTUAL_QTY_BASIC_UNIT is '实收基本单位数量'
/

comment on column OVERSEA_DN_DTL.ACTUAL_QTY_UNIT_SALES is '实收销售单位数量'
/

comment on column OVERSEA_DN_DTL.STORAGE_INFO is '储位信息'
/

comment on column OVERSEA_DN_DTL.REMARKS is '行备注信息'
/

comment on column OVERSEA_DN_DTL.COMMENTS is '行评论信息/预留'
/

comment on column OVERSEA_DN_DTL.BOX_NOTE is '箱码'
/

comment on column OVERSEA_DN_DTL.BOX_NOTE_ITEM is '箱码行号(对应箱码明细表的ITEM字段)'
/

comment on column OVERSEA_DN_DTL.DOMESTIC_ACTUAL_QTY is '国内实际发货基本单位数量'
/

comment on column OVERSEA_DN_DTL.DEMAND_NO is 'SPMS需求来源号'
/

comment on column OVERSEA_DN_DTL.DEMAND_ITEM is 'SPMS需求来源行号'
/

comment on column OVERSEA_DN_DTL.DEMANDER is '需求申请人'
/

comment on column OVERSEA_DN_DTL.DATA_STATUS is '数据状态(默认0能被SAP更新数据；1禁止更新数据；)'
/

comment on column OVERSEA_DN_DTL.DATA_VERSION is '数据版本号'
/

comment on column OVERSEA_DN_DTL.CREATE_TIME is '创建时间'
/

comment on column OVERSEA_DN_DTL.UPDATE_TIME is '更新时间'
/

create trigger TR_OVERSEA_DN_DTL_BEF_UPDATE
    before update
    on OVERSEA_DN_DTL
    for each row
BEGIN
    :new.UPDATE_TIME := TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss');
    :new.DATA_VERSION := DATA_VERSION.nextval;
END;
/




create table OVERSEA_INV_CHECK
(
    UUID VARCHAR2(32) not null
        constraint OVERSEA_INV_CHECK_PK
            primary key
        constraint OVSYS_C0031086
            check ("UUID" IS NOT NULL),
    INVENTORY_CHECK_ID VARCHAR2(32),
    WERKS VARCHAR2(6),
    WHS_LOCATION_CODE VARCHAR2(6),
    INVENTORY_CHECK_TYPE VARCHAR2(2),
    INVENTORY_CHECK_CATEGORY VARCHAR2(2),
    INVENTORY_CHECK_MOLD VARCHAR2(2),
    INVENTORY_CHECK_DATE VARCHAR2(16),
    INVENTORY_CHECK_RESULT VARCHAR2(2),
    IS_REVIEW VARCHAR2(2),
    INVENTORY_REVIEW_ID VARCHAR2(32),
    INVENTORY_CHECK_SURPLUS VARCHAR2(32),
    INVENTORY_CHECK_LOSS VARCHAR2(32),
    CREATE_DATE VARCHAR2(16),
    SPMS_STATUS VARCHAR2(2),
    REQUESTER VARCHAR2(32),
    OPERATOR VARCHAR2(32),
    REVIEWER VARCHAR2(32),
    APPROVER VARCHAR2(32),
    CREATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'),
    UPDATE_USERNAME VARCHAR2(64),
    UPDATE_TIME DATE
)
/

comment on table OVERSEA_INV_CHECK is '海外仓库盘点任务表'
/

comment on column OVERSEA_INV_CHECK.UUID is '海外仓库盘点任务UUID'
/

comment on column OVERSEA_INV_CHECK.INVENTORY_CHECK_ID is '仓库盘点任务单号'
/

comment on column OVERSEA_INV_CHECK.WERKS is '工厂编号'
/

comment on column OVERSEA_INV_CHECK.WHS_LOCATION_CODE is '仓库地点代码'
/

comment on column OVERSEA_INV_CHECK.INVENTORY_CHECK_TYPE is '是否杂货 X-备件货 M-贸易货'
/

comment on column OVERSEA_INV_CHECK.INVENTORY_CHECK_CATEGORY is '盘点类别 1-日常盘点 2-月度盘点 3-动态盘点 4-其它盘点'
/

comment on column OVERSEA_INV_CHECK.INVENTORY_CHECK_MOLD is '盘点类型 1-明盘 2-暗盘'
/

comment on column OVERSEA_INV_CHECK.INVENTORY_CHECK_DATE is '盘点日期'
/

comment on column OVERSEA_INV_CHECK.INVENTORY_CHECK_RESULT is '盘点结果 1-账实相符 2-盘盈 3-盘亏 4-盘盈盘亏'
/

comment on column OVERSEA_INV_CHECK.IS_REVIEW is '是否复盘 1-初盘 2-复盘'
/

comment on column OVERSEA_INV_CHECK.INVENTORY_REVIEW_ID is '复盘单据UUID'
/

comment on column OVERSEA_INV_CHECK.INVENTORY_CHECK_SURPLUS is '盘盈单据号'
/

comment on column OVERSEA_INV_CHECK.INVENTORY_CHECK_LOSS is '盘亏单据号'
/

comment on column OVERSEA_INV_CHECK.CREATE_DATE is '创建日期'
/

comment on column OVERSEA_INV_CHECK.SPMS_STATUS is '任务状态：1-待盘点；2-盘点中；3-复盘中；4-处理中；5-关闭(完成)'
/

comment on column OVERSEA_INV_CHECK.REQUESTER is '创建人工号'
/

comment on column OVERSEA_INV_CHECK.OPERATOR is '申请人'
/

comment on column OVERSEA_INV_CHECK.REVIEWER is '复核人'
/

comment on column OVERSEA_INV_CHECK.APPROVER is '审批人'
/

comment on column OVERSEA_INV_CHECK.CREATE_TIME is '创建时间'
/

comment on column OVERSEA_INV_CHECK.UPDATE_USERNAME is '更新人姓名'
/

comment on column OVERSEA_INV_CHECK.UPDATE_TIME is '更新时间'
/

create trigger TR_OVERSEA_INV_CHECK_BEF
    before update
    on OVERSEA_INV_CHECK
    for each row
BEGIN
    :new.UPDATE_TIME := TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss');
END;
/

create trigger TR_OVERSEA_INV_CHECK_BEF1
    before insert
    on OVERSEA_INV_CHECK
    for each row
DECLARE
    V_NUM1 NUMBER := 0;
BEGIN
    SELECT NVL(TO_NUMBER(SUBSTR(MAX(SOT.INVENTORY_CHECK_ID), -3, 3)), 0) INTO V_NUM1 FROM OVERSEA_INV_CHECK SOT
    WHERE SOT.CREATE_DATE = :NEW.CREATE_DATE
      AND SOT.WERKS = :NEW.WERKS
      AND SOT.WHS_LOCATION_CODE = :NEW.WHS_LOCATION_CODE;
    :NEW.INVENTORY_CHECK_ID := :NEW.WERKS||'-'||:NEW.WHS_LOCATION_CODE||'-'||REPLACE(:NEW.CREATE_DATE, '-')||'-'||SUBSTR(1001 + V_NUM1, 2, 4);
END;
/



create table OVERSEA_INV_CHECK_ACTION
(
    UUID VARCHAR2(32) not null
        constraint OVERSEA_INV_CHECK_ACTION_PK
            primary key
        constraint OVSYS_C0031088
            check ("UUID" IS NOT NULL),
    SPMS_ID VARCHAR2(32),
    INVENTORY_ACTION_TYPE VARCHAR2(6),
    WERKS VARCHAR2(6),
    WHS_LOCATION_CODE VARCHAR2(6),
    INVENTORY_CHECK_ID VARCHAR2(32),
    INVENTORY_CHECK_RESULT VARCHAR2(2),
    INVENTORY_CHECK_TYPE VARCHAR2(2),
    INVENTORY_CHECK_CATEGORY VARCHAR2(2),
    INVENTORY_CHECK_MOLD VARCHAR2(2),
    CREATE_DATE VARCHAR2(16),
    SPMS_STATUS VARCHAR2(2),
    REQUESTER VARCHAR2(32),
    OPERATOR VARCHAR2(32),
    REVIEWER VARCHAR2(32),
    APPROVER VARCHAR2(32),
    CREATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'),
    UPDATE_USERNAME VARCHAR2(64),
    UPDATE_TIME DATE,
    JUSTIFICATION VARCHAR2(255),
    REMARKS VARCHAR2(255),
    POSTING_DATE VARCHAR2(16),
    COST_CENTER VARCHAR2(16),
    MOVE_TYPE VARCHAR2(6),
    VOUCHER_TEXT VARCHAR2(255),
    SAP_VOUCHER_NUMBER VARCHAR2(16),
    SAP_VOUCHER_YEAR VARCHAR2(4),
    OA_WORKFLOW VARCHAR2(16),
    OA_BIANHAO VARCHAR2(32),
    OA_REMARK VARCHAR2(255),
    SUBMIT_OA_DATE VARCHAR2(12),
    OA_APPROVAL_DATE VARCHAR2(12),
    OA_FIELD VARCHAR2(32),
    BUSINESS_TYPE VARCHAR2(16)
)
/

comment on table OVERSEA_INV_CHECK_ACTION is '仓库盘盈盘亏'
/

comment on column OVERSEA_INV_CHECK_ACTION.UUID is '仓库盘盈盘亏任务UUID'
/

comment on column OVERSEA_INV_CHECK_ACTION.SPMS_ID is '仓库盘盈盘亏单号'
/

comment on column OVERSEA_INV_CHECK_ACTION.INVENTORY_ACTION_TYPE is '盘盈盘亏类别 701-盘盈 702-盘亏'
/

comment on column OVERSEA_INV_CHECK_ACTION.WERKS is '工厂编号'
/

comment on column OVERSEA_INV_CHECK_ACTION.WHS_LOCATION_CODE is '仓库地点代码'
/

comment on column OVERSEA_INV_CHECK_ACTION.INVENTORY_CHECK_ID is '盘点单据号'
/

comment on column OVERSEA_INV_CHECK_ACTION.INVENTORY_CHECK_RESULT is '盘点结果 1-账实相符 2-盘盈 3-盘亏 4-盘盈盘亏'
/

comment on column OVERSEA_INV_CHECK_ACTION.INVENTORY_CHECK_TYPE is '是否杂货 X-备件货 M-贸易货'
/

comment on column OVERSEA_INV_CHECK_ACTION.INVENTORY_CHECK_CATEGORY is '盘点类别 1-日常盘点 2-月度盘点 3-动态盘点 4-其它盘点'
/

comment on column OVERSEA_INV_CHECK_ACTION.INVENTORY_CHECK_MOLD is '盘点类型 1-明盘 2-暗盘'
/

comment on column OVERSEA_INV_CHECK_ACTION.CREATE_DATE is '创建日期'
/

comment on column OVERSEA_INV_CHECK_ACTION.SPMS_STATUS is '任务状态：1-草稿;2-提交OA成功；3-提交OA失败；4-OA审批不通过；5-OA审批通过待上传SAP; 6-上传SAP失败 7 -上传SAP成功 8 - 已撤销'
/

comment on column OVERSEA_INV_CHECK_ACTION.REQUESTER is '创建人工号'
/

comment on column OVERSEA_INV_CHECK_ACTION.OPERATOR is '申请人'
/

comment on column OVERSEA_INV_CHECK_ACTION.REVIEWER is '复核人'
/

comment on column OVERSEA_INV_CHECK_ACTION.APPROVER is '审批人'
/

comment on column OVERSEA_INV_CHECK_ACTION.CREATE_TIME is '创建时间'
/

comment on column OVERSEA_INV_CHECK_ACTION.UPDATE_USERNAME is '更新人姓名'
/

comment on column OVERSEA_INV_CHECK_ACTION.UPDATE_TIME is '更新时间'
/

comment on column OVERSEA_INV_CHECK_ACTION.JUSTIFICATION is '申请原因'
/

comment on column OVERSEA_INV_CHECK_ACTION.REMARKS is '备注'
/

comment on column OVERSEA_INV_CHECK_ACTION.POSTING_DATE is '凭证中的过账日期-用户选'
/

comment on column OVERSEA_INV_CHECK_ACTION.COST_CENTER is '成本中心'
/

comment on column OVERSEA_INV_CHECK_ACTION.MOVE_TYPE is '移动类型'
/

comment on column OVERSEA_INV_CHECK_ACTION.VOUCHER_TEXT is '凭证抬头文本'
/

comment on column OVERSEA_INV_CHECK_ACTION.SAP_VOUCHER_NUMBER is 'SAP物料凭证-提交SAP成功后回写'
/

comment on column OVERSEA_INV_CHECK_ACTION.SAP_VOUCHER_YEAR is '会计年份/SAP凭证年份'
/

comment on column OVERSEA_INV_CHECK_ACTION.OA_WORKFLOW is 'OA流程编号'
/

comment on column OVERSEA_INV_CHECK_ACTION.OA_BIANHAO is 'OA单据编号'
/

comment on column OVERSEA_INV_CHECK_ACTION.OA_REMARK is '提交OA审批备注'
/

comment on column OVERSEA_INV_CHECK_ACTION.SUBMIT_OA_DATE is '提交OA日期'
/

comment on column OVERSEA_INV_CHECK_ACTION.OA_APPROVAL_DATE is 'OA审批通过日期'
/

comment on column OVERSEA_INV_CHECK_ACTION.OA_FIELD is 'OA预留字段'
/

comment on column OVERSEA_INV_CHECK_ACTION.BUSINESS_TYPE is '1-工厂请购单 2-海外收货损益审批 3 海外盘点盘盈盘亏审批 4 海外转储审批'
/

create trigger TR_OVERSEA_INV_CHECK_ACTION_BEF
    before update
    on OVERSEA_INV_CHECK_ACTION
    for each row
BEGIN
    :new.UPDATE_TIME := TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss');
END;
/

create trigger TR_OVERSEA_INV_CHECK_ACTION_ID
    before insert
    on OVERSEA_INV_CHECK_ACTION
    for each row
DECLARE
    V_NUM1 NUMBER := 0;
BEGIN
    SELECT NVL(TO_NUMBER(SUBSTR(MAX(SOT.SPMS_ID), -3, 3)), 0) INTO V_NUM1 FROM OVERSEA_INV_CHECK_ACTION SOT
    WHERE SOT.CREATE_DATE = :NEW.CREATE_DATE
      AND SOT.WERKS = :NEW.WERKS
      AND SOT.WHS_LOCATION_CODE = :NEW.WHS_LOCATION_CODE;
    :NEW.SPMS_ID := :NEW.WERKS||'-'||:NEW.WHS_LOCATION_CODE||'-'||REPLACE(:NEW.CREATE_DATE, '-')||'-'||SUBSTR(1001 + V_NUM1, 2, 4);
END;
/



create table OVERSEA_INV_CHECK_ACTION_DTL
(
    UUID VARCHAR2(32),
    TASK_ROW_ID VARCHAR2(6),
    MATERIAL_NO VARCHAR2(32),
    BOX_NOTE VARCHAR2(50),
    INVENTORY_QUANTITY NUMBER,
    CHECK_QUANTITY NUMBER,
    DISCREPANCY NUMBER,
    BASIC_UNIT VARCHAR2(32),
    STORAGE_UUID VARCHAR2(32),
    REMARKS VARCHAR2(256),
    RELATE_ACCOUNT VARCHAR2(16),
    VOUCHER_ITEM_TEXT VARCHAR2(255)
)
/

comment on table OVERSEA_INV_CHECK_ACTION_DTL is '盘盈盘亏明细表'
/

comment on column OVERSEA_INV_CHECK_ACTION_DTL.UUID is '盘盈盘亏UUID'
/

comment on column OVERSEA_INV_CHECK_ACTION_DTL.TASK_ROW_ID is '盘盈盘亏任务行号'
/

comment on column OVERSEA_INV_CHECK_ACTION_DTL.MATERIAL_NO is '物料编号'
/

comment on column OVERSEA_INV_CHECK_ACTION_DTL.BOX_NOTE is '箱码-仅用于自管仓备件管理'
/

comment on column OVERSEA_INV_CHECK_ACTION_DTL.INVENTORY_QUANTITY is '库存数量'
/

comment on column OVERSEA_INV_CHECK_ACTION_DTL.CHECK_QUANTITY is '实盘数量'
/

comment on column OVERSEA_INV_CHECK_ACTION_DTL.DISCREPANCY is '差异数量'
/

comment on column OVERSEA_INV_CHECK_ACTION_DTL.BASIC_UNIT is '基本单位'
/

comment on column OVERSEA_INV_CHECK_ACTION_DTL.STORAGE_UUID is '储位UUID'
/

comment on column OVERSEA_INV_CHECK_ACTION_DTL.REMARKS is '备注'
/

comment on column OVERSEA_INV_CHECK_ACTION_DTL.RELATE_ACCOUNT is '供应商或债权人的帐号'
/

comment on column OVERSEA_INV_CHECK_ACTION_DTL.VOUCHER_ITEM_TEXT is '项目文本'
/






create table OVERSEA_INV_CHECK_DTL
(
    UUID VARCHAR2(32),
    TASK_ROW_ID VARCHAR2(6),
    MATERIAL_NO VARCHAR2(32),
    BOX_NOTE VARCHAR2(50),
    INVENTORY_QUANTITY NUMBER,
    CHECK_QUANTITY NUMBER,
    DISCREPANCY NUMBER,
    BASIC_UNIT VARCHAR2(32),
    STORAGE_UUID VARCHAR2(32),
    REMARKS VARCHAR2(256)
)
/

comment on table OVERSEA_INV_CHECK_DTL is '盘点任务表明细表'
/

comment on column OVERSEA_INV_CHECK_DTL.UUID is '盘点任务UUID'
/

comment on column OVERSEA_INV_CHECK_DTL.TASK_ROW_ID is '盘点任务行号'
/

comment on column OVERSEA_INV_CHECK_DTL.MATERIAL_NO is '物料编号'
/

comment on column OVERSEA_INV_CHECK_DTL.BOX_NOTE is '箱码-仅用于自管仓备件管理'
/

comment on column OVERSEA_INV_CHECK_DTL.INVENTORY_QUANTITY is '库存数量'
/

comment on column OVERSEA_INV_CHECK_DTL.CHECK_QUANTITY is '实盘数量'
/

comment on column OVERSEA_INV_CHECK_DTL.DISCREPANCY is '差异数量'
/

comment on column OVERSEA_INV_CHECK_DTL.BASIC_UNIT is '基本单位'
/

comment on column OVERSEA_INV_CHECK_DTL.STORAGE_UUID is '储位UUID'
/

comment on column OVERSEA_INV_CHECK_DTL.REMARKS is '备注'
/






create table OVERSEA_OPERATE_LOG
(
    AUTO_ID_LOG NUMBER default "OVERSEA_OPERATE_LOG_SEQ"."NEXTVAL" not null
        constraint OVERSEA_OPERATE_LOG_PK
            primary key,
    DOCUMENT_NUMBER VARCHAR2(32),
    LOG_CONTENT VARCHAR2(255),
    LOG_CONTENT_EN VARCHAR2(255),
    LOG_TYPE VARCHAR2(2) default '1',
    REQUEST_PARAM CLOB,
    RETURNED_MESSAGE CLOB,
    REMARK VARCHAR2(1000),
    USER_ID VARCHAR2(16),
    CREATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss')
)
/

comment on table OVERSEA_OPERATE_LOG is '海外仓操作日志表'
/

comment on column OVERSEA_OPERATE_LOG.AUTO_ID_LOG is '自增ID'
/

comment on column OVERSEA_OPERATE_LOG.DOCUMENT_NUMBER is '关联单据号(自增ID或UUID)'
/

comment on column OVERSEA_OPERATE_LOG.LOG_CONTENT is '日志内容描述'
/

comment on column OVERSEA_OPERATE_LOG.LOG_CONTENT_EN is '日志内容英文描述'
/

comment on column OVERSEA_OPERATE_LOG.LOG_TYPE is '日志类型(默认1单据SPMS操作日志;2请求SAP日志;3请求OA日志;)'
/

comment on column OVERSEA_OPERATE_LOG.REQUEST_PARAM is '请求参数'
/

comment on column OVERSEA_OPERATE_LOG.RETURNED_MESSAGE is '返回参数'
/

comment on column OVERSEA_OPERATE_LOG.REMARK is '备注信息'
/

comment on column OVERSEA_OPERATE_LOG.USER_ID is '用户工号'
/

comment on column OVERSEA_OPERATE_LOG.CREATE_TIME is '创建时间'
/





create table OVERSEA_OTHER_DTL
(
    AUTO_ID_OTHER NUMBER default "OVERSEA_OTHER_DTL_SEQ"."NEXTVAL" not null
        constraint OVERSEA_OTHER_DTL_PK
            primary key,
    UUID VARCHAR2(32),
    ITEM VARCHAR2(6),
    WERKS VARCHAR2(8),
    WORKSHOP_CODE VARCHAR2(16),
    WHS_LOCATION_CODE VARCHAR2(6),
    MATERIAL_NO VARCHAR2(32),
    ACTUAL_QTY_BASIC_UNIT NUMBER default 0,
    STORAGE_INFO VARCHAR2(2000),
    BASIC_UNIT VARCHAR2(6),
    COST_CENTER VARCHAR2(32),
    MATERIAL_NO_ADJUSTED VARCHAR2(32),
    ACTUAL_QTY_BASIC_UNIT_ADJUSTED NUMBER default 0,
    STORAGE_INFO_ADJUSTED VARCHAR2(2000),
    SUPPLIER_NO VARCHAR2(16),
    SAP_DELIVERY_NOTE VARCHAR2(16),
    SAP_DELIVERY_NOTE_ITEM VARCHAR2(6),
    REMARKS VARCHAR2(255),
    COMMENTS VARCHAR2(255)
)
/

comment on table OVERSEA_OTHER_DTL is '海外仓其他出入库明细'
/

comment on column OVERSEA_OTHER_DTL.AUTO_ID_OTHER is '其他出入库明细自增ID'
/

comment on column OVERSEA_OTHER_DTL.UUID is '海外仓其他出入库UUID'
/

comment on column OVERSEA_OTHER_DTL.ITEM is '其他出入库明细行号'
/

comment on column OVERSEA_OTHER_DTL.WERKS is '工厂代码'
/

comment on column OVERSEA_OTHER_DTL.WORKSHOP_CODE is '车间代码'
/

comment on column OVERSEA_OTHER_DTL.WHS_LOCATION_CODE is '仓库代码'
/

comment on column OVERSEA_OTHER_DTL.MATERIAL_NO is '物料编号(串货调整时,出库料号)'
/

comment on column OVERSEA_OTHER_DTL.ACTUAL_QTY_BASIC_UNIT is '出入库操作基本单位数量(串货调整时,出库数量)'
/

comment on column OVERSEA_OTHER_DTL.STORAGE_INFO is '储位信息(串货调整时,出库储位)'
/

comment on column OVERSEA_OTHER_DTL.BASIC_UNIT is '基本单位'
/

comment on column OVERSEA_OTHER_DTL.COST_CENTER is '成本中心'
/

comment on column OVERSEA_OTHER_DTL.MATERIAL_NO_ADJUSTED is '调整后的物料编号(仅串货调整时,入库料号)'
/

comment on column OVERSEA_OTHER_DTL.ACTUAL_QTY_BASIC_UNIT_ADJUSTED is '调整后入库操作基本单位数量(仅串货调整时,入库数量)'
/

comment on column OVERSEA_OTHER_DTL.STORAGE_INFO_ADJUSTED is '调整后储位信息(仅串货调整时,入库储位)'
/

comment on column OVERSEA_OTHER_DTL.SUPPLIER_NO is '供应商编号(仅串货调整可选输入)'
/

comment on column OVERSEA_OTHER_DTL.SAP_DELIVERY_NOTE is '交货单号(仅收货多收填写)'
/

comment on column OVERSEA_OTHER_DTL.SAP_DELIVERY_NOTE_ITEM is '交货单行号(仅收货多收填写)'
/

comment on column OVERSEA_OTHER_DTL.REMARKS is '行备注'
/

comment on column OVERSEA_OTHER_DTL.COMMENTS is '行评论'
/






create table OVERSEA_OTHER_TASK
(
    UUID VARCHAR2(32) not null
        constraint OVERSEA_OTHER_TASK_PK
            primary key,
    OPERATE_TITLE VARCHAR2(128),
    SPMS_ID VARCHAR2(32),
    MOVEMENT_TYPE VARCHAR2(8),
    SPMS_STATUS VARCHAR2(2),
    WERKS VARCHAR2(8),
    WORKSHOP_CODE VARCHAR2(16),
    WHS_LOCATION_CODE VARCHAR2(6),
    CREATE_DATE VARCHAR2(16),
    SAP_DELIVERY_NOTE VARCHAR2(16),
    CREATE_USERID VARCHAR2(16),
    COST_CENTER VARCHAR2(32),
    REMARKS VARCHAR2(255),
    COMMENTS VARCHAR2(255),
    PURCHASE_GROUP VARCHAR2(3),
    PURCHASE_ORG VARCHAR2(4),
    POSTING_DATE VARCHAR2(16),
    SAP_VOUCHER_NUMBER VARCHAR2(32),
    SAP_VOUCHER_YEAR VARCHAR2(8),
    CANCEL_POSTING_DATE VARCHAR2(16),
    CANCEL_VOUCHER_NUMBER VARCHAR2(32),
    CANCEL_VOUCHER_YEAR VARCHAR2(8),
    DATA_VERSION NUMBER default "DATA_VERSION"."NEXTVAL",
    CREATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'),
    UPDATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss')
)
/

comment on table OVERSEA_OTHER_TASK is '海外仓其他出入库'
/

comment on column OVERSEA_OTHER_TASK.UUID is '海外仓其他出入库UUID'
/

comment on column OVERSEA_OTHER_TASK.OPERATE_TITLE is '其他出入库操作标题'
/

comment on column OVERSEA_OTHER_TASK.SPMS_ID is '其他出入库SPMS单号'
/

comment on column OVERSEA_OTHER_TASK.MOVEMENT_TYPE is '报废出库551;零价格出库Z55;零价格入库Z56;收货多收Z16;串货调整309;不可抗力出库Z13;IT专用出库Z47;期初库存增加561;期初库存减少562;'
/

comment on column OVERSEA_OTHER_TASK.SPMS_STATUS is 'SPMS状态(0已删除;1保存;2提交/待审批;3审批不通过;4审批通过;5提交SAP失败;6提交SAP成功;)'
/

comment on column OVERSEA_OTHER_TASK.WERKS is '工厂代码'
/

comment on column OVERSEA_OTHER_TASK.WORKSHOP_CODE is '车间代码'
/

comment on column OVERSEA_OTHER_TASK.WHS_LOCATION_CODE is '仓库代码'
/

comment on column OVERSEA_OTHER_TASK.CREATE_DATE is '创建日期'
/

comment on column OVERSEA_OTHER_TASK.SAP_DELIVERY_NOTE is '交货单号(仅收货多收填写)'
/

comment on column OVERSEA_OTHER_TASK.CREATE_USERID is '创建人工号'
/

comment on column OVERSEA_OTHER_TASK.COST_CENTER is '成本中心'
/

comment on column OVERSEA_OTHER_TASK.REMARKS is '备注'
/

comment on column OVERSEA_OTHER_TASK.COMMENTS is '评论'
/

comment on column OVERSEA_OTHER_TASK.PURCHASE_GROUP is '采购组'
/

comment on column OVERSEA_OTHER_TASK.PURCHASE_ORG is '采购组织'
/

comment on column OVERSEA_OTHER_TASK.POSTING_DATE is '过账日期'
/

comment on column OVERSEA_OTHER_TASK.SAP_VOUCHER_NUMBER is 'SAP凭证号'
/

comment on column OVERSEA_OTHER_TASK.SAP_VOUCHER_YEAR is 'SAP凭证年份'
/

comment on column OVERSEA_OTHER_TASK.CANCEL_POSTING_DATE is '撤销SAP过账日期'
/

comment on column OVERSEA_OTHER_TASK.CANCEL_VOUCHER_NUMBER is '撤销SAP凭证'
/

comment on column OVERSEA_OTHER_TASK.CANCEL_VOUCHER_YEAR is '撤销SAP凭证年份'
/

comment on column OVERSEA_OTHER_TASK.DATA_VERSION is '数据版本号'
/

comment on column OVERSEA_OTHER_TASK.CREATE_TIME is '创建时间'
/

comment on column OVERSEA_OTHER_TASK.UPDATE_TIME is '更新时间'
/

create trigger TR_OVERSEA_OTHER_TASK_BEF_UPDATE
    before update
    on OVERSEA_OTHER_TASK
    for each row
BEGIN
    :new.UPDATE_TIME := TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss');
    :new.DATA_VERSION := DATA_VERSION.nextval;
END;
/

create trigger TR_OVERSEA_OTHER_TASK_INSERT
    before insert
    on OVERSEA_OTHER_TASK
    for each row
DECLARE
    V_NUM1 NUMBER := 0;
BEGIN
    SELECT NVL(TO_NUMBER(SUBSTR(MAX(OOT.SPMS_ID), -3, 3)), 0) INTO V_NUM1 FROM OVERSEA_OTHER_TASK OOT
    WHERE OOT.CREATE_DATE = :NEW.CREATE_DATE
      AND OOT.WERKS = :NEW.WERKS
      AND OOT.WHS_LOCATION_CODE = :NEW.WHS_LOCATION_CODE;
    :NEW.SPMS_ID := :NEW.WERKS||'-'||:NEW.WHS_LOCATION_CODE||'-'||REPLACE(:NEW.CREATE_DATE, '-')||'-'||SUBSTR(1001 + V_NUM1, 2, 4);
END;
/



create table OVERSEA_PACKAGE_LIST
(
    UUID_PACKAGE_LIST VARCHAR2(32) not null
        constraint OVERSEA_PACKAGE_LIST_PK
            primary key,
    SAP_PACKAGE_LIST VARCHAR2(16),
    MERCHANDISER VARCHAR2(32),
    CABINET_MODEL VARCHAR2(16),
    FROM_PORT VARCHAR2(64),
    TO_PORT VARCHAR2(64),
    DATA_STATUS VARCHAR2(2) default '0',
    CREATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'),
    UPDATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss')
)
/

comment on table OVERSEA_PACKAGE_LIST is '海外仓装箱单表'
/

comment on column OVERSEA_PACKAGE_LIST.UUID_PACKAGE_LIST is '装箱单uuid'
/

comment on column OVERSEA_PACKAGE_LIST.SAP_PACKAGE_LIST is '装箱单号'
/

comment on column OVERSEA_PACKAGE_LIST.MERCHANDISER is '跟单员'
/

comment on column OVERSEA_PACKAGE_LIST.CABINET_MODEL is '柜型'
/

comment on column OVERSEA_PACKAGE_LIST.FROM_PORT is '起运港'
/

comment on column OVERSEA_PACKAGE_LIST.TO_PORT is '目的港'
/

comment on column OVERSEA_PACKAGE_LIST.DATA_STATUS is '数据状态(默认0能被SAP更新数据；1禁止更新数据；)'
/

comment on column OVERSEA_PACKAGE_LIST.CREATE_TIME is '创建时间'
/

comment on column OVERSEA_PACKAGE_LIST.UPDATE_TIME is '更新时间'
/

create trigger TR_OVERSEA_PACKAGE_LIST_BEF_UPDATE
    before update
    on OVERSEA_PACKAGE_LIST
    for each row
BEGIN
    :new.UPDATE_TIME := TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss');
END;
/





create table OVERSEA_PACKAGE_LIST_BOX
(
    AUTO_ID_PACKAGE_BOX NUMBER default "OVERSEA_PACKAGE_LIST_BOX_SEQ"."NEXTVAL" not null,
    SAP_PACKAGE_LIST VARCHAR2(16),
    SAP_PACKAGE_LIST_ITEM VARCHAR2(6),
    BOX_NOTE VARCHAR2(50),
    BOX_NOTE_ITEM VARCHAR2(6),
    BOX_STATUS VARCHAR2(2) default '0',
    DATA_STATUS VARCHAR2(2) default '0',
    ARRIVAL_DATE VARCHAR2(16),
    UPDATE_USERID VARCHAR2(16),
    CREATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'),
    UPDATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'),
    WERKS_RECEIVE VARCHAR2(6),
    WHS_LOCATION_CODE_RECEIVE VARCHAR2(6),
    MATERIAL_NO VARCHAR2(32),
    RECEIVABLE_QTY_BASIC_UNIT NUMBER default 0,
    ACTUAL_QTY_BASIC_UNIT NUMBER default 0,
    BASIC_UNIT VARCHAR2(6),
    DEMAND_NO VARCHAR2(32),
    DEMAND_ITEM VARCHAR2(6),
    DEMANDER VARCHAR2(128)
)
/

comment on table OVERSEA_PACKAGE_LIST_BOX is '海外仓收货装箱单箱码关系表'
/

comment on column OVERSEA_PACKAGE_LIST_BOX.AUTO_ID_PACKAGE_BOX is 'SPMS唯一自增ID'
/

comment on column OVERSEA_PACKAGE_LIST_BOX.SAP_PACKAGE_LIST is '装箱单号'
/

comment on column OVERSEA_PACKAGE_LIST_BOX.SAP_PACKAGE_LIST_ITEM is '装箱单行号'
/

comment on column OVERSEA_PACKAGE_LIST_BOX.BOX_NOTE is '箱码'
/

comment on column OVERSEA_PACKAGE_LIST_BOX.BOX_NOTE_ITEM is '箱码行号(对应箱码明细表的ITEM字段)'
/

comment on column OVERSEA_PACKAGE_LIST_BOX.BOX_STATUS is '箱码状态(默认0待到货;1已到货;)'
/

comment on column OVERSEA_PACKAGE_LIST_BOX.DATA_STATUS is '数据状态(默认0能被SAP更新数据；1禁止更新数据；)'
/

comment on column OVERSEA_PACKAGE_LIST_BOX.ARRIVAL_DATE is '箱码到货日期'
/

comment on column OVERSEA_PACKAGE_LIST_BOX.UPDATE_USERID is '最后更新人(确认到货人工号)'
/

comment on column OVERSEA_PACKAGE_LIST_BOX.CREATE_TIME is '创建时间'
/

comment on column OVERSEA_PACKAGE_LIST_BOX.UPDATE_TIME is '更新时间'
/

comment on column OVERSEA_PACKAGE_LIST_BOX.WERKS_RECEIVE is '实际收货工厂'
/

comment on column OVERSEA_PACKAGE_LIST_BOX.WHS_LOCATION_CODE_RECEIVE is '实际收货仓库'
/

comment on column OVERSEA_PACKAGE_LIST_BOX.MATERIAL_NO is '物料编号'
/

comment on column OVERSEA_PACKAGE_LIST_BOX.RECEIVABLE_QTY_BASIC_UNIT is '基本单位发货数量/海外应收基本单位数量'
/

comment on column OVERSEA_PACKAGE_LIST_BOX.ACTUAL_QTY_BASIC_UNIT is '实收基本单位数量'
/

comment on column OVERSEA_PACKAGE_LIST_BOX.BASIC_UNIT is '基本单位'
/

comment on column OVERSEA_PACKAGE_LIST_BOX.DEMAND_NO is '需求来源号'
/

comment on column OVERSEA_PACKAGE_LIST_BOX.DEMAND_ITEM is '需求来源行号'
/

comment on column OVERSEA_PACKAGE_LIST_BOX.DEMANDER is '需求申请人'
/

create unique index OVERSEA_PACKAGE_LIST_BOX_AUTO_ID_PACKAGE_BOX_UINDEX
    on OVERSEA_PACKAGE_LIST_BOX (AUTO_ID_PACKAGE_BOX)
/

alter table OVERSEA_PACKAGE_LIST_BOX
    add constraint OVERSEA_PACKAGE_LIST_BOX_PK
        primary key (AUTO_ID_PACKAGE_BOX)
/

create trigger TR_OVERSEA_PACKAGE_LIST_BOX_BEF_UPDATE
    before update
    on OVERSEA_PACKAGE_LIST_BOX
    for each row
BEGIN
    :new.UPDATE_TIME := TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss');
END;
/






create table OVERSEA_PACKAGE_LIST_DTL
(
    AUTO_ID_PACKAGE_LIST NUMBER default "OVERSEA_PACKAGE_LIST_DTL_SEQ"."NEXTVAL" not null
        constraint OVERSEA_PACKAGE_LIST_TDL_PK
            primary key,
    SAP_PACKAGE_LIST VARCHAR2(16),
    SAP_PACKAGE_LIST_ITEM VARCHAR2(6),
    SAP_DELIVERY_NOTE VARCHAR2(16),
    PURCHASE_NO VARCHAR2(16),
    PURCHASE_NO_ITEM VARCHAR2(6),
    DATA_STATUS VARCHAR2(2),
    CREATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'),
    UPDATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss')
)
/

comment on table OVERSEA_PACKAGE_LIST_DTL is '海外装箱单明细表'
/

comment on column OVERSEA_PACKAGE_LIST_DTL.AUTO_ID_PACKAGE_LIST is '装箱单明细自增ID'
/

comment on column OVERSEA_PACKAGE_LIST_DTL.SAP_PACKAGE_LIST is '装箱单号'
/

comment on column OVERSEA_PACKAGE_LIST_DTL.SAP_PACKAGE_LIST_ITEM is '装箱单行号'
/

comment on column OVERSEA_PACKAGE_LIST_DTL.SAP_DELIVERY_NOTE is '交货单号'
/

comment on column OVERSEA_PACKAGE_LIST_DTL.PURCHASE_NO is '公司间采购订单号'
/

comment on column OVERSEA_PACKAGE_LIST_DTL.PURCHASE_NO_ITEM is '公司间采购订单行项目'
/

comment on column OVERSEA_PACKAGE_LIST_DTL.DATA_STATUS is '数据状态(默认0能被SAP更新数据；1禁止更新数据；)'
/

comment on column OVERSEA_PACKAGE_LIST_DTL.CREATE_TIME is '创建时间'
/

comment on column OVERSEA_PACKAGE_LIST_DTL.UPDATE_TIME is '更新时间'
/

create trigger TR_OVERSEA_PACKAGE_LIST_DTL_BEF_UPDATE
    before update
    on OVERSEA_PACKAGE_LIST_DTL
    for each row
BEGIN
    :new.UPDATE_TIME := TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss');
END;
/






create table OVERSEA_PURCHASE_ORDER
(
    UUID_PURCHASE_ORDER VARCHAR2(32) not null
        constraint OVERSEA_PURCHASE_ORDER_PK
            primary key,
    SAP_PURCHASE_ORDER VARCHAR2(16),
    BUSINESS_TYPE VARCHAR2(4),
    SAP_CREATE_DATE VARCHAR2(16),
    SUPPLIER_CODE VARCHAR2(16),
    SUPPLIER_DESC VARCHAR2(255),
    WERKS_DELIVERY VARCHAR2(6),
    PURCHASE_GROUP VARCHAR2(3),
    PURCHASE_ORG VARCHAR2(4),
    CREATE_USER VARCHAR2(32),
    VOUCHER_TYPE VARCHAR2(6),
    SAP_REMARK VARCHAR2(255),
    DATA_STATUS VARCHAR2(2) default '0',
    DATA_VERSION NUMBER default "DATA_VERSION"."NEXTVAL",
    CREATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'),
    UPDATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss')
)
/

comment on table OVERSEA_PURCHASE_ORDER is '海外仓采购订单表'
/

comment on column OVERSEA_PURCHASE_ORDER.UUID_PURCHASE_ORDER is '采购订单表uuid'
/

comment on column OVERSEA_PURCHASE_ORDER.SAP_PURCHASE_ORDER is '采购凭证号PO'
/

comment on column OVERSEA_PURCHASE_ORDER.BUSINESS_TYPE is '业务标识(01空运人带;02本地采购;03海外转储;05退货订单;)'
/

comment on column OVERSEA_PURCHASE_ORDER.SAP_CREATE_DATE is 'SAP凭证日期'
/

comment on column OVERSEA_PURCHASE_ORDER.SUPPLIER_CODE is '供应商编号'
/

comment on column OVERSEA_PURCHASE_ORDER.SUPPLIER_DESC is '供应商名称'
/

comment on column OVERSEA_PURCHASE_ORDER.WERKS_DELIVERY is '发货/转出工厂'
/

comment on column OVERSEA_PURCHASE_ORDER.PURCHASE_GROUP is '采购组(当业务标识为01、03时，满足下列数据的值才取，240海外转储，260为空运，250人带)'
/

comment on column OVERSEA_PURCHASE_ORDER.PURCHASE_ORG is '采购组织'
/

comment on column OVERSEA_PURCHASE_ORDER.CREATE_USER is '建单人'
/

comment on column OVERSEA_PURCHASE_ORDER.VOUCHER_TYPE is '凭证类型(当业务标识为02时，ZN03为本地采购订单)'
/

comment on column OVERSEA_PURCHASE_ORDER.SAP_REMARK is 'SAP表头文本'
/

comment on column OVERSEA_PURCHASE_ORDER.DATA_STATUS is '数据状态(默认0能被SAP更新数据；1禁止更新数据；)'
/

comment on column OVERSEA_PURCHASE_ORDER.DATA_VERSION is '数据版本号'
/

comment on column OVERSEA_PURCHASE_ORDER.CREATE_TIME is '创建时间'
/

comment on column OVERSEA_PURCHASE_ORDER.UPDATE_TIME is '更新时间'
/

create trigger TR_OVERSEA_PURCHASE_ORDER_BEF_UPDATE
    before update
    on OVERSEA_PURCHASE_ORDER
    for each row
BEGIN
    :new.UPDATE_TIME := TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss');
    :new.DATA_VERSION := DATA_VERSION.nextval;
END;
/






create table OVERSEA_PURCHASE_ORDER_DTL
(
    AUTO_ID_PURCHASE_ORDER NUMBER default "OVERSEA_PURCHASE_ORDER_DTL_SEQ"."NEXTVAL" not null
        constraint OVERSEA_PURCHASE_ORDER_DTL_PK
            primary key,
    SAP_PURCHASE_ORDER VARCHAR2(16),
    SAP_PURCHASE_ORDER_ITEM VARCHAR2(6),
    DELIVERY_DATE VARCHAR2(16),
    WERKS_RECEIVE VARCHAR2(6),
    WHS_LOCATION_CODE_RECEIVE VARCHAR2(6),
    MATERIAL_NO VARCHAR2(32),
    MATERIAL_ZH_DESC VARCHAR2(255),
    BASIC_UNIT VARCHAR2(6),
    RECEIVABLE_QTY_BASIC_UNIT NUMBER default 0,
    RETURN_MARK VARCHAR2(2),
    DATA_STATUS VARCHAR2(2) default '0',
    DATA_VERSION NUMBER default "DATA_VERSION"."NEXTVAL",
    CREATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'),
    UPDATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss')
)
/

comment on table OVERSEA_PURCHASE_ORDER_DTL is '海外仓采购订单表明细'
/

comment on column OVERSEA_PURCHASE_ORDER_DTL.AUTO_ID_PURCHASE_ORDER is '采购订单明细自增ID'
/

comment on column OVERSEA_PURCHASE_ORDER_DTL.SAP_PURCHASE_ORDER is '采购凭证号PO'
/

comment on column OVERSEA_PURCHASE_ORDER_DTL.SAP_PURCHASE_ORDER_ITEM is '采购凭证号PO行号'
/

comment on column OVERSEA_PURCHASE_ORDER_DTL.DELIVERY_DATE is 'DN交货日期'
/

comment on column OVERSEA_PURCHASE_ORDER_DTL.WERKS_RECEIVE is '收货/转入工厂'
/

comment on column OVERSEA_PURCHASE_ORDER_DTL.WHS_LOCATION_CODE_RECEIVE is '收货/转入仓库地址'
/

comment on column OVERSEA_PURCHASE_ORDER_DTL.MATERIAL_NO is '物料编号'
/

comment on column OVERSEA_PURCHASE_ORDER_DTL.MATERIAL_ZH_DESC is '物料描述'
/

comment on column OVERSEA_PURCHASE_ORDER_DTL.BASIC_UNIT is '基本单位'
/

comment on column OVERSEA_PURCHASE_ORDER_DTL.RECEIVABLE_QTY_BASIC_UNIT is '交货数量/应收基本单位数量'
/

comment on column OVERSEA_PURCHASE_ORDER_DTL.RETURN_MARK is '退货标识'
/

comment on column OVERSEA_PURCHASE_ORDER_DTL.DATA_STATUS is '数据状态(默认0能被SAP更新数据；1禁止更新数据；)'
/

comment on column OVERSEA_PURCHASE_ORDER_DTL.DATA_VERSION is '数据版本号'
/

comment on column OVERSEA_PURCHASE_ORDER_DTL.CREATE_TIME is '创建时间'
/

comment on column OVERSEA_PURCHASE_ORDER_DTL.UPDATE_TIME is '更新时间'
/

create trigger TR_OVERSEA_PURCHASE_ORDER_DTL_BEF_UPDATE
    before update
    on OVERSEA_PURCHASE_ORDER_DTL
    for each row
BEGIN
    :new.UPDATE_TIME := TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss');
    :new.DATA_VERSION := DATA_VERSION.nextval;
END;
/








create table OVERSEA_REQUISITION
(
    AUTO_ID_REQUISITION NUMBER default "OVERSEA_REQUISITION_SEQ"."NEXTVAL",
    UUID_REQUISITION VARCHAR2(32) not null
        constraint OVERSEA_REQUISITION_PK
            primary key,
    SPMS_ID VARCHAR2(32),
    CREATE_DATE VARCHAR2(16),
    BUSINESS_TYPE VARCHAR2(32),
    SPMS_STATUS VARCHAR2(2) default '1',
    CREATE_USERID VARCHAR2(16),
    WERKS VARCHAR2(16),
    WORKSHOP_CODE VARCHAR2(32),
    WHS_LOCATION_CODE VARCHAR2(16),
    RELATE_DOCUMENT VARCHAR2(32),
    COST_CENTER VARCHAR2(32),
    DATA_VERSION NUMBER default "DATA_VERSION"."NEXTVAL",
    REMARKS VARCHAR2(255),
    COMMENTS VARCHAR2(255),
    CREATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'),
    UPDATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'),
    COST_CENTER_NAME VARCHAR2(255)
)
/

comment on table OVERSEA_REQUISITION is '领用申请表'
/

comment on column OVERSEA_REQUISITION.AUTO_ID_REQUISITION is '领用申请表自增ID'
/

comment on column OVERSEA_REQUISITION.UUID_REQUISITION is '领用申请UUID'
/

comment on column OVERSEA_REQUISITION.SPMS_ID is '领用申请单号'
/

comment on column OVERSEA_REQUISITION.CREATE_DATE is '创建日期'
/

comment on column OVERSEA_REQUISITION.BUSINESS_TYPE is '业务类型'
/

comment on column OVERSEA_REQUISITION.SPMS_STATUS is '单据状态(0已删除;1待提交;2待审批;3审批通过;4审批拒绝;5已生成出库任务;)'
/

comment on column OVERSEA_REQUISITION.CREATE_USERID is '申请人'
/

comment on column OVERSEA_REQUISITION.WERKS is '工厂代码'
/

comment on column OVERSEA_REQUISITION.WORKSHOP_CODE is '车间代码'
/

comment on column OVERSEA_REQUISITION.WHS_LOCATION_CODE is '仓库地点代码'
/

comment on column OVERSEA_REQUISITION.RELATE_DOCUMENT is '来源单号'
/

comment on column OVERSEA_REQUISITION.COST_CENTER is '成本中心'
/

comment on column OVERSEA_REQUISITION.DATA_VERSION is '数据版本号'
/

comment on column OVERSEA_REQUISITION.REMARKS is '备注/申请原因'
/

comment on column OVERSEA_REQUISITION.COMMENTS is '评论/标题'
/

comment on column OVERSEA_REQUISITION.CREATE_TIME is '创建时间'
/

comment on column OVERSEA_REQUISITION.UPDATE_TIME is '更新时间'
/

comment on column OVERSEA_REQUISITION.COST_CENTER_NAME is '成本中心名称'
/

create trigger TR_OVERSEA_REQUISITION_BEF_UPDATE
    before update
    on OVERSEA_REQUISITION
    for each row
BEGIN
    :new.UPDATE_TIME := TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss');
    :new.DATA_VERSION := DATA_VERSION.nextval;
END;
/

create trigger TR_OVERSEA_REQUISITION_INSERT
    before insert
    on OVERSEA_REQUISITION
    for each row
DECLARE
    V_NUM1 NUMBER := 0;
BEGIN
    SELECT NVL(TO_NUMBER(SUBSTR(MAX(ORQ.SPMS_ID), -3, 3)), 0) INTO V_NUM1 FROM OVERSEA_REQUISITION ORQ
    WHERE ORQ.CREATE_DATE = :NEW.CREATE_DATE
      AND ORQ.WERKS = :NEW.WERKS
      AND ORQ.WORKSHOP_CODE = :NEW.WORKSHOP_CODE;
    :NEW.SPMS_ID := :NEW.WERKS||'-'||:NEW.WORKSHOP_CODE||'-'||REPLACE(:NEW.CREATE_DATE, '-')||'-'||SUBSTR(1001 + V_NUM1, 2, 4);
END;
/






create table OVERSEA_REQUISITION_DTL
(
    AUTO_ID_REQUISITION_DTL NUMBER default "OVERSEA_REQUISITION_DTL_SEQ"."NEXTVAL" not null
        constraint OVERSEA_REQUISITION_DTL_PK
            primary key,
    UUID_REQUISITION VARCHAR2(32),
    ITEM NUMBER,
    WERKS VARCHAR2(16),
    WORKSHOP_CODE VARCHAR2(16),
    WHS_LOCATION_CODE VARCHAR2(16),
    MATERIAL_NO VARCHAR2(32),
    APPLY_QTY_BASIC_UNIT NUMBER,
    ACTUAL_QTY_BASIC_UNIT NUMBER,
    BASIC_UNIT VARCHAR2(6),
    APPLY_QTY_UNIT_SALES NUMBER,
    ACTUAL_QTY_UNIT_SALES NUMBER,
    UNIT_SALES VARCHAR2(6),
    REMARKS VARCHAR2(255),
    STORAGE_INFO VARCHAR2(2000),
    MOVEMENT_TYPE VARCHAR2(12),
    ACTUAL_MOVEMENT_TYPE VARCHAR2(12)
)
/

comment on table OVERSEA_REQUISITION_DTL is '领用申请表明细'
/

comment on column OVERSEA_REQUISITION_DTL.AUTO_ID_REQUISITION_DTL is '领用申请明细表自增ID'
/

comment on column OVERSEA_REQUISITION_DTL.UUID_REQUISITION is '领用申请UUID'
/

comment on column OVERSEA_REQUISITION_DTL.ITEM is '明细行号'
/

comment on column OVERSEA_REQUISITION_DTL.WERKS is '工厂代码'
/

comment on column OVERSEA_REQUISITION_DTL.WORKSHOP_CODE is '车间代码'
/

comment on column OVERSEA_REQUISITION_DTL.WHS_LOCATION_CODE is '仓库地点代码'
/

comment on column OVERSEA_REQUISITION_DTL.MATERIAL_NO is '物料编号'
/

comment on column OVERSEA_REQUISITION_DTL.APPLY_QTY_BASIC_UNIT is '申请基本单位数量'
/

comment on column OVERSEA_REQUISITION_DTL.ACTUAL_QTY_BASIC_UNIT is '实际基本单位数量'
/

comment on column OVERSEA_REQUISITION_DTL.BASIC_UNIT is '基本单位'
/

comment on column OVERSEA_REQUISITION_DTL.APPLY_QTY_UNIT_SALES is '申请销售单位数量'
/

comment on column OVERSEA_REQUISITION_DTL.ACTUAL_QTY_UNIT_SALES is '实际销售单位数量'
/

comment on column OVERSEA_REQUISITION_DTL.UNIT_SALES is '销售单位'
/

comment on column OVERSEA_REQUISITION_DTL.REMARKS is '明细备注'
/

comment on column OVERSEA_REQUISITION_DTL.STORAGE_INFO is '储位信息'
/

comment on column OVERSEA_REQUISITION_DTL.MOVEMENT_TYPE is '申请物料移动类型'
/

comment on column OVERSEA_REQUISITION_DTL.ACTUAL_MOVEMENT_TYPE is '实际移动类型(生成出库任务时)'
/






create table OVERSEA_WHS_DUMP
(
    WHS_DUMP_UUID VARCHAR2(32) not null
        constraint OVERSEA_WHS_DUMP_PK
            primary key,
    WHS_DUMP_NO VARCHAR2(32),
    WERKS_DUMP_IN VARCHAR2(6),
    WHS_LOCATION_CODE_DUMP_IN VARCHAR2(6),
    WERKS_DUMP_OUT VARCHAR2(6),
    WHS_LOCATION_CODE_DUMP_OUT VARCHAR2(6),
    CREATE_DATE VARCHAR2(12),
    SPMS_STATUS VARCHAR2(2),
    OA_WORKFLOW VARCHAR2(16),
    OA_BIANHAO VARCHAR2(32),
    OA_REMARK VARCHAR2(255),
    SUBMIT_OA_DATE VARCHAR2(12),
    OA_APPROVAL_DATE VARCHAR2(12),
    OA_FIELD VARCHAR2(16),
    DUMP_OUT_REMARK VARCHAR2(255),
    DUMP_OUT_DATE VARCHAR2(12),
    SAP_PURCHASE_NO VARCHAR2(32),
    PURCHASE_POSTING_DATE VARCHAR2(16),
    PURCHASE_VOUCHER_YEAR VARCHAR2(8),
    SAP_DELIVERY_NOTE VARCHAR2(32),
    POSTING_DATE VARCHAR2(12),
    SAP_VOUCHER_YEAR VARCHAR2(8),
    CANCEL_VOUCHER_NUMBER VARCHAR2(32),
    CANCEL_POSTING_DATE VARCHAR2(12),
    CANCEL_VOUCHER_YEAR VARCHAR2(8),
    BUSINESS_TYPE VARCHAR2(16),
    VOUCHER_TYPE VARCHAR2(16),
    PURCHASE_GROUP VARCHAR2(16),
    PURCHASE_ORG VARCHAR2(16),
    RETURN_MARK VARCHAR2(16),
    DELIVERY_DATE VARCHAR2(16),
    DELIVERY_TYPE VARCHAR2(12),
    MOVEMENT_TYPE VARCHAR2(8),
    COST_CENTER VARCHAR2(8),
    COMMENTS VARCHAR2(32),
    CREATE_USERID VARCHAR2(12),
    DATA_VERSION NUMBER default "DATA_VERSION"."NEXTVAL",
    CREATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'),
    UPDATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss')
)
/

comment on table OVERSEA_WHS_DUMP is '海外仓转储任务表'
/

comment on column OVERSEA_WHS_DUMP.WHS_DUMP_UUID is '转储任务UUID'
/

comment on column OVERSEA_WHS_DUMP.WHS_DUMP_NO is '转储任务单号'
/

comment on column OVERSEA_WHS_DUMP.WERKS_DUMP_IN is '转入工厂代码'
/

comment on column OVERSEA_WHS_DUMP.WHS_LOCATION_CODE_DUMP_IN is '转入仓库代码'
/

comment on column OVERSEA_WHS_DUMP.WERKS_DUMP_OUT is '转出工厂代码'
/

comment on column OVERSEA_WHS_DUMP.WHS_LOCATION_CODE_DUMP_OUT is '转出仓库代码'
/

comment on column OVERSEA_WHS_DUMP.CREATE_DATE is '创建日期'
/

comment on column OVERSEA_WHS_DUMP.SPMS_STATUS is '海外SPMS转储单据状态:0删除；1草稿；2待审批；3SPMS审批通过(提交OA);4OA驳回(操作同1);5OA审批通过(出库中);6出库完成;7提交SAP获取PO失败;8提交SAP获取PO;9提交SAP获取DN失败;10提交SAP获取DN成功(扣转出方库存);11转出已撤销(撤到状态5出库中，SAP撤回成功则加回库存);'
/

comment on column OVERSEA_WHS_DUMP.OA_WORKFLOW is 'OA流程编号'
/

comment on column OVERSEA_WHS_DUMP.OA_BIANHAO is 'OA单据编号'
/

comment on column OVERSEA_WHS_DUMP.OA_REMARK is '提交OA审批备注'
/

comment on column OVERSEA_WHS_DUMP.SUBMIT_OA_DATE is '提交OA日期'
/

comment on column OVERSEA_WHS_DUMP.OA_APPROVAL_DATE is 'OA审批通过日期'
/

comment on column OVERSEA_WHS_DUMP.OA_FIELD is 'OA预留字段'
/

comment on column OVERSEA_WHS_DUMP.DUMP_OUT_REMARK is '转出备注信息'
/

comment on column OVERSEA_WHS_DUMP.DUMP_OUT_DATE is '转出完成日期'
/

comment on column OVERSEA_WHS_DUMP.SAP_PURCHASE_NO is 'SAP采购凭证号'
/

comment on column OVERSEA_WHS_DUMP.PURCHASE_POSTING_DATE is '获取采购凭证号过账日期'
/

comment on column OVERSEA_WHS_DUMP.PURCHASE_VOUCHER_YEAR is '采购凭证年份'
/

comment on column OVERSEA_WHS_DUMP.SAP_DELIVERY_NOTE is 'SAP交货单号'
/

comment on column OVERSEA_WHS_DUMP.POSTING_DATE is '获取交货单号采购凭证过账日期'
/

comment on column OVERSEA_WHS_DUMP.SAP_VOUCHER_YEAR is '交货单凭证年份'
/

comment on column OVERSEA_WHS_DUMP.CANCEL_VOUCHER_NUMBER is '撤销SAP凭证号'
/

comment on column OVERSEA_WHS_DUMP.CANCEL_POSTING_DATE is '撤销SAP过账日期'
/

comment on column OVERSEA_WHS_DUMP.CANCEL_VOUCHER_YEAR is '撤销SAP凭证年份'
/

comment on column OVERSEA_WHS_DUMP.BUSINESS_TYPE is '业务标识'
/

comment on column OVERSEA_WHS_DUMP.VOUCHER_TYPE is '凭证类型/订单类型(采购)公司内ZU01/公司间ZC01'
/

comment on column OVERSEA_WHS_DUMP.PURCHASE_GROUP is '采购组-240'
/

comment on column OVERSEA_WHS_DUMP.PURCHASE_ORG is '采购组织-1000'
/

comment on column OVERSEA_WHS_DUMP.RETURN_MARK is '退货标识'
/

comment on column OVERSEA_WHS_DUMP.DELIVERY_DATE is '项目交货日期/需求到货日期'
/

comment on column OVERSEA_WHS_DUMP.DELIVERY_TYPE is '交货类型'
/

comment on column OVERSEA_WHS_DUMP.MOVEMENT_TYPE is '移动类型'
/

comment on column OVERSEA_WHS_DUMP.COST_CENTER is '成本中心'
/

comment on column OVERSEA_WHS_DUMP.COMMENTS is '预留字段'
/

comment on column OVERSEA_WHS_DUMP.CREATE_USERID is '创建人工号'
/

comment on column OVERSEA_WHS_DUMP.DATA_VERSION is '数据版本号'
/

comment on column OVERSEA_WHS_DUMP.CREATE_TIME is '创建时间'
/

comment on column OVERSEA_WHS_DUMP.UPDATE_TIME is '更新时间'
/

create trigger TR_OVERSEA_WHS_DUMP_BEF_UPDATE
    before update
    on OVERSEA_WHS_DUMP
    for each row
BEGIN
    :new.UPDATE_TIME := TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss');
    :new.DATA_VERSION := DATA_VERSION.nextval;
END;
/

create trigger TR_OVERSEA_WHS_DUMP_INSERT
    before insert
    on OVERSEA_WHS_DUMP
    for each row
DECLARE
    V_NUM1 NUMBER := 0;
BEGIN
    SELECT NVL(TO_NUMBER(SUBSTR(MAX(OWD.WHS_DUMP_NO), -3, 3)), 0) INTO V_NUM1 FROM OVERSEA_WHS_DUMP OWD
    WHERE OWD.CREATE_DATE = :NEW.CREATE_DATE
      AND OWD.WERKS_DUMP_OUT = :NEW.WERKS_DUMP_OUT
      AND OWD.WHS_LOCATION_CODE_DUMP_OUT = :NEW.WHS_LOCATION_CODE_DUMP_OUT;
    :NEW.WHS_DUMP_NO := :NEW.WERKS_DUMP_OUT||'-'||:NEW.WHS_LOCATION_CODE_DUMP_OUT||'-'||REPLACE(:NEW.CREATE_DATE, '-')||'-'||SUBSTR(1001 + V_NUM1, 2, 4);
END;
/







create table OVERSEA_WHS_DUMP_DTL
(
    AUTO_ID_DUMP_DTL NUMBER default "OVERSEA_WHS_DUMP_DTL_SEQ"."NEXTVAL" not null
        constraint OVERSEA_WHS_DUMP_DTL_PK
            primary key,
    WHS_DUMP_UUID VARCHAR2(32),
    ITEM NUMBER,
    MATERIAL_NO VARCHAR2(32),
    BASIC_UNIT VARCHAR2(6),
    UNIT_SALES VARCHAR2(6),
    APPLY_QTY_BASIC_UNIT NUMBER,
    APPLY_QTY_UNIT_SALES NUMBER,
    APPLY_REMARK VARCHAR2(255),
    ACTUAL_QTY_BASIC_UNIT NUMBER,
    ACTUAL_QTY_UNIT_SALES NUMBER,
    ACTUAL_STORAGE_INFO VARCHAR2(2000),
    ACTUAL_REMARK VARCHAR2(255)
)
/

comment on table OVERSEA_WHS_DUMP_DTL is '海外转储任务明细表'
/

comment on column OVERSEA_WHS_DUMP_DTL.AUTO_ID_DUMP_DTL is '海外转储明细自增ID'
/

comment on column OVERSEA_WHS_DUMP_DTL.WHS_DUMP_UUID is '转储任务UUID'
/

comment on column OVERSEA_WHS_DUMP_DTL.ITEM is '转储任务行号'
/

comment on column OVERSEA_WHS_DUMP_DTL.MATERIAL_NO is '物料编号'
/

comment on column OVERSEA_WHS_DUMP_DTL.BASIC_UNIT is '基本单位'
/

comment on column OVERSEA_WHS_DUMP_DTL.UNIT_SALES is '销售单位'
/

comment on column OVERSEA_WHS_DUMP_DTL.APPLY_QTY_BASIC_UNIT is '申请转出基本单位数量'
/

comment on column OVERSEA_WHS_DUMP_DTL.APPLY_QTY_UNIT_SALES is '申请转出销售单位数量'
/

comment on column OVERSEA_WHS_DUMP_DTL.APPLY_REMARK is '申请行备注信息'
/

comment on column OVERSEA_WHS_DUMP_DTL.ACTUAL_QTY_BASIC_UNIT is '实际转出基本单位数量'
/

comment on column OVERSEA_WHS_DUMP_DTL.ACTUAL_QTY_UNIT_SALES is '实际转出销售单位数量'
/

comment on column OVERSEA_WHS_DUMP_DTL.ACTUAL_STORAGE_INFO is '实际转出储位信息'
/

comment on column OVERSEA_WHS_DUMP_DTL.ACTUAL_REMARK is '实际转出行备注信息'
/






create table OVERSEA_WHS_IN
(
    UUID_WHS_IN VARCHAR2(32) not null
        constraint OVERSEA_WHS_IN_PK
            primary key,
    WHS_IN_NO VARCHAR2(32),
    SAP_PACKAGE_LIST VARCHAR2(16),
    SAP_DELIVERY_NOTE VARCHAR2(16),
    WERKS VARCHAR2(10),
    WHS_LOCATION_CODE VARCHAR2(10),
    SPMS_STATUS VARCHAR2(2) default '0',
    CREATE_DATE VARCHAR2(12),
    CREATE_USERID VARCHAR2(32),
    REMARKS VARCHAR2(255),
    COMMENTS VARCHAR2(255),
    POSTING_DATE VARCHAR2(16),
    SAP_VOUCHER_NUMBER VARCHAR2(32),
    SAP_VOUCHER_YEAR VARCHAR2(16),
    CANCEL_POSTING_DATE VARCHAR2(16),
    CANCEL_VOUCHER_NUMBER VARCHAR2(32),
    CANCEL_VOUCHER_YEAR VARCHAR2(16),
    DATA_VERSION NUMBER default "DATA_VERSION"."NEXTVAL",
    CREATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'),
    UPDATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'),
    CABINET_NUMBER VARCHAR2(50)
)
/

comment on table OVERSEA_WHS_IN is '海外仓入库任务表头'
/

comment on column OVERSEA_WHS_IN.UUID_WHS_IN is '入库任务UUID'
/

comment on column OVERSEA_WHS_IN.WHS_IN_NO is '入库任务单号'
/

comment on column OVERSEA_WHS_IN.SAP_PACKAGE_LIST is '装箱单号'
/

comment on column OVERSEA_WHS_IN.SAP_DELIVERY_NOTE is '交货单号'
/

comment on column OVERSEA_WHS_IN.WERKS is '入库任务工厂'
/

comment on column OVERSEA_WHS_IN.WHS_LOCATION_CODE is '入库任务仓库库存地点'
/

comment on column OVERSEA_WHS_IN.SPMS_STATUS is '入库任务状态(默认0待入库;1入库中;2入库完成;3审批通过;4提交SAP成功;5提交SAP失败;6已撤销;7关闭;)'
/

comment on column OVERSEA_WHS_IN.CREATE_DATE is '创建日期'
/

comment on column OVERSEA_WHS_IN.CREATE_USERID is '创建人工号'
/

comment on column OVERSEA_WHS_IN.REMARKS is '入库任务备注'
/

comment on column OVERSEA_WHS_IN.COMMENTS is '入库任务评论'
/

comment on column OVERSEA_WHS_IN.POSTING_DATE is '提交SAP过账日期'
/

comment on column OVERSEA_WHS_IN.SAP_VOUCHER_NUMBER is 'SAP物料凭证号'
/

comment on column OVERSEA_WHS_IN.SAP_VOUCHER_YEAR is 'SAP物料凭证年份'
/

comment on column OVERSEA_WHS_IN.CANCEL_POSTING_DATE is '撤销提交SAP过账日期'
/

comment on column OVERSEA_WHS_IN.CANCEL_VOUCHER_NUMBER is '撤销提交SAP物料凭证号'
/

comment on column OVERSEA_WHS_IN.CANCEL_VOUCHER_YEAR is '撤销提交SAP物料凭证年份'
/

comment on column OVERSEA_WHS_IN.DATA_VERSION is '数据版本号'
/

comment on column OVERSEA_WHS_IN.CREATE_TIME is '创建时间'
/

comment on column OVERSEA_WHS_IN.UPDATE_TIME is '更新时间'
/

comment on column OVERSEA_WHS_IN.CABINET_NUMBER is '柜号'
/

create trigger TR_OVERSEA_WHS_IN_BEF_UPDATE
    before update
    on OVERSEA_WHS_IN
    for each row
BEGIN
    :new.UPDATE_TIME := TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss');
    :new.DATA_VERSION := DATA_VERSION.nextval;
END;
/

create trigger TR_OVERSEA_WHS_IN_BEF_INSERT
    before insert
    on OVERSEA_WHS_IN
    for each row
DECLARE
    V_NUM1 NUMBER := 0;
BEGIN
    SELECT NVL(TO_NUMBER(SUBSTR(MAX(WI.WHS_IN_NO), 15, 17)), 0) INTO V_NUM1 FROM OVERSEA_WHS_IN WI
    WHERE WI.CREATE_DATE = :NEW.CREATE_DATE AND WI.WERKS = :NEW.WERKS;
    :NEW.WHS_IN_NO := :NEW.WERKS||'-'||REPLACE(:NEW.CREATE_DATE, '-', '')|| '-'|| SUBSTR(1001 + V_NUM1, 2, 4);
END;
/






create table OVERSEA_WHS_IN_DTL
(
    AUTO_ID_WHS_IN_DTL NUMBER default "ITEM_AUTO_ID_SEQ"."NEXTVAL" not null
        constraint OVERSEA_WHS_IN_DTL_PK
            primary key,
    UUID_WHS_IN VARCHAR2(32),
    ITEM VARCHAR2(6),
    MATERIAL_NO VARCHAR2(32),
    BASIC_UNIT VARCHAR2(6),
    UNIT_SALES VARCHAR2(6),
    RECEIVABLE_QTY_BASIC_UNIT NUMBER,
    RECEIVABLE_QTY_UNIT_SALES NUMBER,
    ACTUAL_QTY_BASIC_UNIT NUMBER,
    ACTUAL_QTY_UNIT_SALES NUMBER,
    STORAGE_INFO VARCHAR2(2000),
    ITEM_REMARKS VARCHAR2(255),
    SAP_DELIVERY_NOTE VARCHAR2(16),
    SAP_DELIVERY_NOTE_ITEM VARCHAR2(6),
    RECEIVABLE_WERKS VARCHAR2(16),
    RECEIVABLE_WHS_LOCATION_CODE VARCHAR2(16),
    ACTUAL_WERKS VARCHAR2(16),
    ACTUAL_WHS_LOCATION_CODE VARCHAR2(16),
    BOX_NOTE VARCHAR2(50),
    BOX_NOTE_ITEM VARCHAR2(6)
)
/

comment on table OVERSEA_WHS_IN_DTL is '海外仓入库任务明细'
/

comment on column OVERSEA_WHS_IN_DTL.AUTO_ID_WHS_IN_DTL is '入库任务明细自增ID'
/

comment on column OVERSEA_WHS_IN_DTL.UUID_WHS_IN is '入库任务UUID'
/

comment on column OVERSEA_WHS_IN_DTL.ITEM is '入库任务行号'
/

comment on column OVERSEA_WHS_IN_DTL.MATERIAL_NO is '物料编号'
/

comment on column OVERSEA_WHS_IN_DTL.BASIC_UNIT is '基本单位'
/

comment on column OVERSEA_WHS_IN_DTL.UNIT_SALES is '销售单位'
/

comment on column OVERSEA_WHS_IN_DTL.RECEIVABLE_QTY_BASIC_UNIT is '应收基本单位数量'
/

comment on column OVERSEA_WHS_IN_DTL.RECEIVABLE_QTY_UNIT_SALES is '应收销售单位数量'
/

comment on column OVERSEA_WHS_IN_DTL.ACTUAL_QTY_BASIC_UNIT is '实收基本单位数量'
/

comment on column OVERSEA_WHS_IN_DTL.ACTUAL_QTY_UNIT_SALES is '实收销售单位数量'
/

comment on column OVERSEA_WHS_IN_DTL.STORAGE_INFO is '储位信息'
/

comment on column OVERSEA_WHS_IN_DTL.ITEM_REMARKS is 'SPMS行项目备注'
/

comment on column OVERSEA_WHS_IN_DTL.SAP_DELIVERY_NOTE is '交货单号'
/

comment on column OVERSEA_WHS_IN_DTL.SAP_DELIVERY_NOTE_ITEM is '交货单行号'
/

comment on column OVERSEA_WHS_IN_DTL.RECEIVABLE_WERKS is '收货工厂——应收'
/

comment on column OVERSEA_WHS_IN_DTL.RECEIVABLE_WHS_LOCATION_CODE is '收货库存地点——应收'
/

comment on column OVERSEA_WHS_IN_DTL.ACTUAL_WERKS is '收货工厂——实收'
/

comment on column OVERSEA_WHS_IN_DTL.ACTUAL_WHS_LOCATION_CODE is '收货库存地点——实收'
/

comment on column OVERSEA_WHS_IN_DTL.BOX_NOTE is '箱码'
/

comment on column OVERSEA_WHS_IN_DTL.BOX_NOTE_ITEM is '箱码明细行号'
/







create table OVERSEA_WHS_OUT
(
    AUTO_ID_WHS_OUT NUMBER default "OVERSEA_WHS_OUT_SEQ"."NEXTVAL",
    UUID_WHS_OUT VARCHAR2(32) not null
        constraint OVERSEA_WHS_OUT_PK
            primary key,
    SPMS_ID VARCHAR2(32),
    CREATE_DATE VARCHAR2(16),
    MOVEMENT_TYPE VARCHAR2(32),
    SPMS_STATUS VARCHAR2(2) default '1',
    CREATE_USERID VARCHAR2(16),
    WERKS VARCHAR2(16),
    WORKSHOP_CODE VARCHAR2(16),
    WHS_LOCATION_CODE VARCHAR2(16),
    RELATE_DOCUMENT VARCHAR2(32),
    COST_CENTER VARCHAR2(32),
    DATA_VERSION NUMBER default "DATA_VERSION"."NEXTVAL",
    REMARKS VARCHAR2(255),
    COMMENTS VARCHAR2(255),
    POSTING_DATE VARCHAR2(16),
    SAP_VOUCHER_NUMBER VARCHAR2(32),
    SAP_VOUCHER_YEAR VARCHAR2(16),
    CANCEL_POSTING_DATE VARCHAR2(16),
    SAP_CANCEL_VOUCHER_NUMBER VARCHAR2(32),
    SAP_CANCEL_VOUCHER_YEAR VARCHAR2(16),
    CREATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'),
    UPDATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'),
    COST_CENTER_NAME VARCHAR2(255)
)
/

comment on table OVERSEA_WHS_OUT is '海外出库任务表'
/

comment on column OVERSEA_WHS_OUT.AUTO_ID_WHS_OUT is '出库任务表自增ID'
/

comment on column OVERSEA_WHS_OUT.UUID_WHS_OUT is '出库任务UUID'
/

comment on column OVERSEA_WHS_OUT.SPMS_ID is '出库任务单号'
/

comment on column OVERSEA_WHS_OUT.CREATE_DATE is '创建日期'
/

comment on column OVERSEA_WHS_OUT.MOVEMENT_TYPE is '移动类型'
/

comment on column OVERSEA_WHS_OUT.SPMS_STATUS is '单据状态(0已删除;1出库中;2出库完成;3提交SAP成功;4提交SAP失败;5已撤销;)'
/

comment on column OVERSEA_WHS_OUT.CREATE_USERID is '创建人'
/

comment on column OVERSEA_WHS_OUT.WERKS is '工厂代码'
/

comment on column OVERSEA_WHS_OUT.WORKSHOP_CODE is '车间代码'
/

comment on column OVERSEA_WHS_OUT.WHS_LOCATION_CODE is '仓库地点代码'
/

comment on column OVERSEA_WHS_OUT.RELATE_DOCUMENT is '来源单号/关联单据号'
/

comment on column OVERSEA_WHS_OUT.COST_CENTER is '成本中心'
/

comment on column OVERSEA_WHS_OUT.DATA_VERSION is '数据版本号'
/

comment on column OVERSEA_WHS_OUT.REMARKS is '备注'
/

comment on column OVERSEA_WHS_OUT.COMMENTS is '评论/出库申请标题'
/

comment on column OVERSEA_WHS_OUT.POSTING_DATE is 'SAP过账日期'
/

comment on column OVERSEA_WHS_OUT.SAP_VOUCHER_NUMBER is 'SAP物料凭证'
/

comment on column OVERSEA_WHS_OUT.SAP_VOUCHER_YEAR is 'SAP物料凭证年份'
/

comment on column OVERSEA_WHS_OUT.CANCEL_POSTING_DATE is 'SAP撤销过账日期'
/

comment on column OVERSEA_WHS_OUT.SAP_CANCEL_VOUCHER_NUMBER is 'SAP撤销凭证'
/

comment on column OVERSEA_WHS_OUT.SAP_CANCEL_VOUCHER_YEAR is 'SAP撤销凭证年份'
/

comment on column OVERSEA_WHS_OUT.CREATE_TIME is '创建时间'
/

comment on column OVERSEA_WHS_OUT.UPDATE_TIME is '更新时间'
/

comment on column OVERSEA_WHS_OUT.COST_CENTER_NAME is '成本中心名称'
/

create trigger TR_OVERSEA_WHS_OUT_BEF_UPDATE
    before update
    on OVERSEA_WHS_OUT
    for each row
BEGIN
    :new.UPDATE_TIME := TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss');
    :new.DATA_VERSION := DATA_VERSION.nextval;
END;
/

create trigger TR_OVERSEA_WHS_OUT_INSERT
    before insert
    on OVERSEA_WHS_OUT
    for each row
DECLARE
    V_NUM1 NUMBER := 0;
BEGIN
    SELECT NVL(TO_NUMBER(SUBSTR(MAX(OWO.SPMS_ID), -3, 3)), 0) INTO V_NUM1 FROM OVERSEA_WHS_OUT OWO
    WHERE OWO.CREATE_DATE = :NEW.CREATE_DATE
      AND OWO.WERKS = :NEW.WERKS
      AND OWO.WHS_LOCATION_CODE = :NEW.WHS_LOCATION_CODE;
    :NEW.SPMS_ID := :NEW.WERKS||'-'||:NEW.WHS_LOCATION_CODE||'-'||REPLACE(:NEW.CREATE_DATE, '-')||'-'||SUBSTR(1001 + V_NUM1, 2, 4);
END;
/






create table OVERSEA_WHS_OUT_DTL
(
    AUTO_ID_WHS_OUT_DTL NUMBER default "OVERSEA_WHS_OUT_DTL_SEQ"."NEXTVAL" not null
        primary key,
    UUID_WHS_OUT VARCHAR2(32),
    ITEM NUMBER,
    WERKS VARCHAR2(16),
    WORKSHOP_CODE VARCHAR2(16),
    WHS_LOCATION_CODE VARCHAR2(16),
    MATERIAL_NO VARCHAR2(32),
    ISSUE_QTY_BASIC_UNIT NUMBER,
    ACTUAL_QTY_BASIC_UNIT NUMBER,
    BASIC_UNIT VARCHAR2(6),
    ISSUE_QTY_UNIT_SALES NUMBER,
    ACTUAL_QTY_UNIT_SALES NUMBER,
    UNIT_SALES VARCHAR2(6),
    REMARKS VARCHAR2(255),
    STORAGE_INFO VARCHAR2(2000),
    MOVEMENT_TYPE VARCHAR2(10),
    RELATE_DOCUMENT VARCHAR2(32),
    RELATE_DOCUMENT_ITEM VARCHAR2(6)
)
/

comment on table OVERSEA_WHS_OUT_DTL is '出库任务表明细'
/

comment on column OVERSEA_WHS_OUT_DTL.AUTO_ID_WHS_OUT_DTL is '出库任务表明细自增ID'
/

comment on column OVERSEA_WHS_OUT_DTL.UUID_WHS_OUT is '出库任务UUID'
/

comment on column OVERSEA_WHS_OUT_DTL.ITEM is '明细行号'
/

comment on column OVERSEA_WHS_OUT_DTL.WERKS is '工厂代码'
/

comment on column OVERSEA_WHS_OUT_DTL.WORKSHOP_CODE is '车间代码'
/

comment on column OVERSEA_WHS_OUT_DTL.WHS_LOCATION_CODE is '仓库地点代码'
/

comment on column OVERSEA_WHS_OUT_DTL.MATERIAL_NO is '物料编号'
/

comment on column OVERSEA_WHS_OUT_DTL.ISSUE_QTY_BASIC_UNIT is '应出基本单位数量'
/

comment on column OVERSEA_WHS_OUT_DTL.ACTUAL_QTY_BASIC_UNIT is '实际基本单位数量'
/

comment on column OVERSEA_WHS_OUT_DTL.BASIC_UNIT is '基本单位'
/

comment on column OVERSEA_WHS_OUT_DTL.ISSUE_QTY_UNIT_SALES is '应出销售单位数量'
/

comment on column OVERSEA_WHS_OUT_DTL.ACTUAL_QTY_UNIT_SALES is '实际销售单位数量'
/

comment on column OVERSEA_WHS_OUT_DTL.UNIT_SALES is '销售单位'
/

comment on column OVERSEA_WHS_OUT_DTL.REMARKS is '明细备注'
/

comment on column OVERSEA_WHS_OUT_DTL.STORAGE_INFO is '储位信息'
/

comment on column OVERSEA_WHS_OUT_DTL.MOVEMENT_TYPE is '物料移动类型'
/

comment on column OVERSEA_WHS_OUT_DTL.RELATE_DOCUMENT is '关联单号/领用申请单号'
/

comment on column OVERSEA_WHS_OUT_DTL.RELATE_DOCUMENT_ITEM is '关联单号/领用申请单号明细行号'
/







create table SAFETY_INVENTORY
(
    UUID VARCHAR2(32) not null
        constraint SAFETY_INVENTORY_PK
            primary key,
    WERKS VARCHAR2(6),
    MATERIAL_NO VARCHAR2(32),
    BASIC_UNIT VARCHAR2(6),
    SAFETY_QTY_BASIC_UNIT NUMBER default 0,
    SPMS_STATUS VARCHAR2(2) default '1',
    STANDARD_CONSUMPTION_LAST_YEAR NUMBER default 0,
    SAFETY_INVENTORY_YEAR NUMBER default 0.5,
    EMERGENCY_RESERVE_BASIC_UNIT NUMBER default 0,
    REMARKS VARCHAR2(255),
    COMMENTS VARCHAR2(255),
    DATA_VERSION NUMBER default "DATA_VERSION"."NEXTVAL",
    CREATE_USERID VARCHAR2(32),
    UPDATE_USERID VARCHAR2(32),
    CREATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'),
    UPDATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'),
    WHS_LOCATION_CODE VARCHAR2(12)
)
/

comment on table SAFETY_INVENTORY is '安全库存表'
/

comment on column SAFETY_INVENTORY.UUID is 'UUID唯一主键'
/

comment on column SAFETY_INVENTORY.WERKS is '工厂代码'
/

comment on column SAFETY_INVENTORY.MATERIAL_NO is '物料编号'
/

comment on column SAFETY_INVENTORY.BASIC_UNIT is '基本单位'
/

comment on column SAFETY_INVENTORY.SAFETY_QTY_BASIC_UNIT is '安全库存基本单位数量'
/

comment on column SAFETY_INVENTORY.SPMS_STATUS is '安全库存数据状态,默认1有效,0无效'
/

comment on column SAFETY_INVENTORY.STANDARD_CONSUMPTION_LAST_YEAR is '上年度标准耗用量'
/

comment on column SAFETY_INVENTORY.SAFETY_INVENTORY_YEAR is '安全库存年数(默认0.5年)'
/

comment on column SAFETY_INVENTORY.EMERGENCY_RESERVE_BASIC_UNIT is '应急储备量(基本单位)'
/

comment on column SAFETY_INVENTORY.REMARKS is '备注'
/

comment on column SAFETY_INVENTORY.COMMENTS is '评论/预留'
/

comment on column SAFETY_INVENTORY.DATA_VERSION is '数据版本号'
/

comment on column SAFETY_INVENTORY.CREATE_USERID is '创建人工号'
/

comment on column SAFETY_INVENTORY.UPDATE_USERID is '更新人工号'
/

comment on column SAFETY_INVENTORY.CREATE_TIME is '创建时间'
/

comment on column SAFETY_INVENTORY.UPDATE_TIME is '更新时间'
/

comment on column SAFETY_INVENTORY.WHS_LOCATION_CODE is '仓库地点代码'
/

create trigger TR_SAFETY_INVENTORY_BEF_UPDATE
    before update
    on SAFETY_INVENTORY
    for each row
BEGIN
    :new.UPDATE_TIME := TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss');
    :new.DATA_VERSION := DATA_VERSION.nextval;
END;
/






create table MOVEMENT_TYPE
(
    UUID VARCHAR2(32) not null
        constraint MOVEMENT_TYPE_PK
            primary key,
    MOVEMENT_TYPE VARCHAR2(16),
    MOVEMENT_TYPE_ZH_DESC VARCHAR2(255),
    MOVEMENT_TYPE_EN_DESC VARCHAR2(255),
    APPLICABLE_BUSINESS VARCHAR2(255),
    REMARKS VARCHAR2(255),
    CREATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'),
    UPDATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss')
)
/

comment on table MOVEMENT_TYPE is '领用移动类型'
/

comment on column MOVEMENT_TYPE.UUID is '唯一uuid'
/

comment on column MOVEMENT_TYPE.MOVEMENT_TYPE is 'SAP出入库类型'
/

comment on column MOVEMENT_TYPE.APPLICABLE_BUSINESS is '适用业务内容'
/

comment on column MOVEMENT_TYPE.REMARKS is '备注'
/

comment on column MOVEMENT_TYPE.CREATE_TIME is '创建时间'
/

comment on column MOVEMENT_TYPE.UPDATE_TIME is '更新时间'
/

create trigger TR_MOVEMENT_TYPE_BEF_UPDATE
    before update
    on MOVEMENT_TYPE
    for each row
BEGIN
    :new.UPDATE_TIME := TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss');
END;
/




-- ======================================== 以上为 2022.01.15 生产环境执行 DDL SQL 语句============================================



create table OVERSEA_WHS_MOVE
(
    UUID VARCHAR2(32) not null
        constraint OVERSEA_WHS_MOVE_PK
            primary key,
    WHS_MOVE_NO VARCHAR2(32),
    SUBJECT VARCHAR2(255),
    CREATE_DATE VARCHAR2(16),
    SPMS_STATUS VARCHAR2(2) default '1',
    MOVEMENT_TYPE VARCHAR2(6),
    WERKS_MOVE_OUT VARCHAR2(12),
    WHS_LOCATION_CODE_MOVE_OUT VARCHAR2(12),
    REMARK_MOVE_OUT VARCHAR2(1000),
    WERKS_MOVE_IN VARCHAR2(12),
    WHS_LOCATION_CODE_MOVE_IN VARCHAR2(12),
    REMARK_MOVE_IN VARCHAR2(1000),
    RELATE_DOCUMENT VARCHAR2(32),
    REMARKS VARCHAR2(255),
    COMMENTS VARCHAR2(255),
    POSTING_DATE VARCHAR2(16),
    SAP_VOUCHER_NUMBER VARCHAR2(32),
    SAP_VOUCHER_YEAR VARCHAR2(8),
    CANCEL_POSTING_DATE VARCHAR2(16),
    CANCEL_VOUCHER_NUMBER VARCHAR2(32),
    CANCEL_VOUCHER_YEAR VARCHAR2(8),
    CREATE_USERID VARCHAR2(32),
    DATA_VERSION NUMBER default "DATA_VERSION"."NEXTVAL",
    CREATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'),
    UPDATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss')
)
/

comment on table OVERSEA_WHS_MOVE is '海外仓调拨业务'
/

comment on column OVERSEA_WHS_MOVE.UUID is '调拨任务UUID'
/

comment on column OVERSEA_WHS_MOVE.WHS_MOVE_NO is '调拨任务单号'
/

comment on column OVERSEA_WHS_MOVE.SUBJECT is '标题'
/

comment on column OVERSEA_WHS_MOVE.CREATE_DATE is '创建日期'
/

comment on column OVERSEA_WHS_MOVE.SPMS_STATUS is '调拨任务状态(0删除;1保存;2提交SPMS/库存更新;3提交SAP失败;4提交SAP成功;5调拨任务已撤销;)'
/

comment on column OVERSEA_WHS_MOVE.MOVEMENT_TYPE is '移动类型'
/

comment on column OVERSEA_WHS_MOVE.WERKS_MOVE_OUT is '调拨调出工厂代码'
/

comment on column OVERSEA_WHS_MOVE.WHS_LOCATION_CODE_MOVE_OUT is '调拨调出仓库代码'
/

comment on column OVERSEA_WHS_MOVE.REMARK_MOVE_OUT is '调拨调出备注'
/

comment on column OVERSEA_WHS_MOVE.WERKS_MOVE_IN is '调拨调入工厂代码'
/

comment on column OVERSEA_WHS_MOVE.WHS_LOCATION_CODE_MOVE_IN is '调拨调入仓库代码'
/

comment on column OVERSEA_WHS_MOVE.REMARK_MOVE_IN is '调拨调入备注'
/

comment on column OVERSEA_WHS_MOVE.RELATE_DOCUMENT is '关联单据号'
/

comment on column OVERSEA_WHS_MOVE.REMARKS is '备注/预留'
/

comment on column OVERSEA_WHS_MOVE.COMMENTS is '评论/预留'
/

comment on column OVERSEA_WHS_MOVE.POSTING_DATE is '过账日期'
/

comment on column OVERSEA_WHS_MOVE.SAP_VOUCHER_NUMBER is 'SAP凭证号'
/

comment on column OVERSEA_WHS_MOVE.SAP_VOUCHER_YEAR is 'SAP过账年份'
/

comment on column OVERSEA_WHS_MOVE.CANCEL_POSTING_DATE is '撤销SAP过账日期'
/

comment on column OVERSEA_WHS_MOVE.CANCEL_VOUCHER_NUMBER is '撤销SAP凭证'
/

comment on column OVERSEA_WHS_MOVE.CANCEL_VOUCHER_YEAR is '撤销SAP凭证年份'
/

comment on column OVERSEA_WHS_MOVE.CREATE_USERID is '创建人工号'
/

comment on column OVERSEA_WHS_MOVE.DATA_VERSION is '数据版本号'
/

comment on column OVERSEA_WHS_MOVE.CREATE_TIME is '创建时间'
/

comment on column OVERSEA_WHS_MOVE.UPDATE_TIME is '更新时间'
/

create trigger TR_OVERSEA_WHS_MOVE_BEF
    before insert
    on OVERSEA_WHS_MOVE
    for each row
DECLARE
    V_NUM1 NUMBER := 0;
BEGIN
    SELECT NVL(TO_NUMBER(SUBSTR(MAX(WM.WHS_MOVE_NO), -3, 3)), 0) INTO V_NUM1 FROM WHS_MOVE WM
    WHERE WM.CREATE_DATE = :NEW.CREATE_DATE
      AND WM.WERKS_MOVE_OUT = :NEW.WERKS_MOVE_OUT;
    :NEW.WHS_MOVE_NO := :NEW.WERKS_MOVE_OUT||'-'||REPLACE(:NEW.CREATE_DATE, '-')||'-'|| SUBSTR(1001 + V_NUM1, 2, 4);
END;
/

create trigger TR_OVERSEA_WHS_MOVE_BEF_UPDATE
    before update
    on OVERSEA_WHS_MOVE
    for each row
BEGIN
    :new.UPDATE_TIME := TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss');
    :new.DATA_VERSION := DATA_VERSION.nextval;
END;
/


create table OVERSEA_WHS_MOVE_DTL
(
    UUID VARCHAR2(32),
    ITEM_NO VARCHAR2(6),
    MATERIAL_NO VARCHAR2(32),
    QTY_MOVE_OUT NUMBER default 0,
    STORAGE_INFO_MOVE_OUT VARCHAR2(2000) default '[]',
    ACTUAL_QTY_MOVE_OUT NUMBER default 0,
    ACTUAL_STORAGE_INFO_MOVE_OUT VARCHAR2(2000) default '[]',
    REMARK_MOVE_OUT VARCHAR2(255),
    ACTUAL_QTY_MOVE_IN NUMBER default 0,
    ACTUAL_STORAGE_INFO_MOVE_IN VARCHAR2(2000) default '[]',
    REMARK_MOVE_IN VARCHAR2(255),
    SUPPLIER_NO VARCHAR2(16),
    COST_CENTER VARCHAR2(16),
    REMARKS VARCHAR2(255),
    COMMENTS VARCHAR2(255)
)
/

comment on table OVERSEA_WHS_MOVE_DTL is '海外仓调拨任务明细'
/

comment on column OVERSEA_WHS_MOVE_DTL.UUID is '调拨任务UUID'
/

comment on column OVERSEA_WHS_MOVE_DTL.ITEM_NO is '调拨明细行号'
/

comment on column OVERSEA_WHS_MOVE_DTL.MATERIAL_NO is '物料编号'
/

comment on column OVERSEA_WHS_MOVE_DTL.QTY_MOVE_OUT is '计划调拨调出数量'
/

comment on column OVERSEA_WHS_MOVE_DTL.STORAGE_INFO_MOVE_OUT is '计划调拨调出储位信息'
/

comment on column OVERSEA_WHS_MOVE_DTL.ACTUAL_QTY_MOVE_OUT is '实际调拨调出数量'
/

comment on column OVERSEA_WHS_MOVE_DTL.ACTUAL_STORAGE_INFO_MOVE_OUT is '实际调拨调出储位信息'
/

comment on column OVERSEA_WHS_MOVE_DTL.REMARK_MOVE_OUT is '调拨调出备注'
/

comment on column OVERSEA_WHS_MOVE_DTL.ACTUAL_QTY_MOVE_IN is '实际调拨调入数量'
/

comment on column OVERSEA_WHS_MOVE_DTL.ACTUAL_STORAGE_INFO_MOVE_IN is '实际调拨调入储位信息'
/

comment on column OVERSEA_WHS_MOVE_DTL.REMARK_MOVE_IN is '调拨调入备注'
/

comment on column OVERSEA_WHS_MOVE_DTL.SUPPLIER_NO is '供应商账号'
/

comment on column OVERSEA_WHS_MOVE_DTL.COST_CENTER is '成本中心'
/

comment on column OVERSEA_WHS_MOVE_DTL.REMARKS is '备注/预留'
/

comment on column OVERSEA_WHS_MOVE_DTL.COMMENTS is '评论/预留'
/















